topic Re: Proc Import changes excel date to sasdate in SAS Enterprise Guide

Proc Import changes excel date to sasdate

jen123 — Fri, 20 Mar 2015 15:23:26 GMT

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!

Re: Proc Import changes excel date to sasdate

jen123 — Fri, 20 Mar 2015 15:34:47 GMT

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?

Re: Proc Import changes excel date to sasdate

ChrisHemedinger — Fri, 20 Mar 2015 15:41:22 GMT

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;

Re: Proc Import changes excel date to sasdate

jen123 — Fri, 20 Mar 2015 15:48:58 GMT

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?

Re: Proc Import changes excel date to sasdate

ChrisHemedinger — Fri, 20 Mar 2015 19:55:30 GMT

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

Re: Proc Import changes excel date to sasdate

comatt1 — Tue, 24 Mar 2015 18:54:49 GMT

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;

Re: Proc Import changes excel date to sasdate

Tom — Tue, 24 Mar 2015 20:57:57 GMT

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;