title: SAS: Define type when importing .xlsx with PROC IMPORT
body: <p><strong>Questions:</strong> How do I define the variable type of variables being imported from a .xlsx file when using PROC IMPORT?</p>
  
  <hr>
  
  <p><strong>My work</strong></p>
  
  <p>I am using SAS v9.4.  So far as I'm aware, it is vanilla SAS.  I do not have SAS/ACCESS etc.</p>
  
  <p>My data looks like this:</p>
  
  <pre><code>ID1        ID2  MONTH   YEAR    QTR VAR1    VAR2
  ABC_1234   1    1       2010    1   869     3988
  ABC_1235   12   2       2010    1   639     3144
  ABC_1236   13   3       2010    2   698     3714
  ABC_1237   45   4       2010    2   630     3213
  </code></pre>
  
  <p>The procedure I am running is:</p>
  
  <pre><code>proc import out=rawdata
      datafile = "c:\rawdata.xlsx"
          dbms = xlsx replace;
  
      format ID1 $9. ;
      format ID2 $3. ;
      format MONTH best2. ;
      format YEAR best4. ;
      format QTR best1. ;
      format VAR1 best3. ;
      format VAR2 best4. ;
  run;
  </code></pre>
  
  <p>When I run this step, I get the following log output:</p>
  
  <blockquote>
    <p>ERROR: You are trying to use the character format $ with the numeric variable ID2 in data set WORK.RAWDATA.</p>
  </blockquote>
  
  <p>What this seems to tell me is that SAS automatically assigns the variable type.  I want to be able to control it manually.  I cannot find documentation which explains how to do this.  INFORMAT, LENGTH, and INPUT statements do not seem to work for PROC IMPORT.  </p>
  
  <p>I am using PROC IMPORT because it has yielded the greatest success with .xlsx files overall.  Two possible solutions I can think of are 1) convert .xlsx to .csv and use INFILE in a DATA step and 2) bring the data in as numeric and convert it to character in a later step.  I dislike the first solution because it requires me to manually manipulate the data, a potential source of error (such as leading zeros being removed).  I dislike the second because it may unintentionally introduce errors (again, such as with leading zeros) and introduces extraneous work.</p>