Proc Import changes excel date to sasdate

[jen123]

Hello, I have an excel file (XLSX) with 72 columns representing month (i.e. Jan-14, Feb-14, etc).  When I use proc import, the column header changes to sasdate:  jan-14 = 41640, etc. How can I get PROC IMPORT to show Jan-14, etc as the column header/variable? Here is my code: Proc Import Datafile="&xlsx./projecttracking.xlsx" Out=others DBMS=xlsx Replace; Sheet='Data_Others'; Getnames=Yes; DATAROW=2; MIXED=Yes; GuessingRows=32797; Run; Also, I am unable to use DATAROW in the code above.  Is DATAROW not compatible with DBMS=XSLX? Thank yoU!

[jen123]

I copy/paste my code from sas to notepad/MS word/used the "Advanced Editor" function on this website and none displays my code as I intended it to.  Rather it's in sentence structure. What am I doing wrong?

[ChrisHemedinger]

Here's how I add code to a post:

- In SAS Enterprise Guide, right-click on a code selection and select Copy HTML Source to Clipboard

- In the message editor here, click HTML from the toolbar

- In the HTML view, find a snug spot after a <p></p> tag combo.

- Paste content

- Quick! Select Show Full editor again before your eyes bleed from the crazy HTML coding you see

Result:

Proc Import Datafile="&xlsx./projecttracking.xlsx"
  Out=others DBMS=xlsx Replace;
    Sheet='Data_Others';
    Getnames=Yes;
    DATAROW=2;
    MIXED=Yes;
    GuessingRows=32797;
Run;

[jen123]

Thanks Chris!  I copied your suggestion over to my 'cheat sheet". Now that Chris has put my code in an easier to read format: 1.  I have an excel file (XLSX) with 72 columns representing month (i.e. Jan-14, Feb-14, etc).  When I use proc import, the column header changes to sasdate:  jan-14 = 41640, etc. How can I get PROC IMPORT to show Jan-14, etc as the column header/variable? 2.  I am unable to use DATAROW in the code above.  Is DATAROW not compatible with DBMS=XSLX?

[ChrisHemedinger]

You might try RANGE= instead -- recommended in the documentation for DBMS=XLSX.   Also, if your columns are going to have nontraditional names like "Jan-14" (with the hyphen, you'll have to set:

options validvarname=any;

When you run the import step.

Chris

[Tom]

I would suggest that you do something in Excel to store character strings for your column headers instead of formatted numbers.

As an example I made a small sheet with 4 columns 'ID', 'Jan-15', 'Feb-15' and 2015-03-15 formatted to display as Mar-15.

Here is the result from PROC IMPORT.

269  proc import datafile='c:\downloads\date_example.xlsx'

270   dbms=xlsx

271   out=example

272   replace

273  ;

274  run;

NOTE:    Variable Name Change.  Jan-15 -> Jan_15

NOTE:    Variable Name Change.  Feb-15 -> Feb_15

NOTE:    Variable Name Change.  42078 -> _42078

NOTE: The import data set has 1 observations and 4 variables.

NOTE: WORK.EXAMPLE data set was successfully created.

NOTE: PROCEDURE IMPORT used (Total process time):

      real time           0.06 seconds

      cpu time            0.03 seconds

If you cannot fix then read the column headers from the metadata (or PROC CONTENTS output) and convert to what ever format you want and generate a RENAME statement to change the variable names.

proc sql noprint ;

  select catx('=',name,substr(put(input(substr(name,2),8.)+'01JAN1900'd-2,date9.),3))

    into :rename

    from contents

    where name like '^_%' escape '^'

  ;

quit;

[comatt1]

have you attempted to just format as a date object; and format how you like after doing this?

Proc print data=work.a;

     var date;

     format data mmddyy10.;

run;