DATA Step, Macro, Functions and more

PROC IMPORT - Changing Variable Type from Character to DateTime

Reply
Contributor
Posts: 71

PROC IMPORT - Changing Variable Type from Character to DateTime

I'm trying to import several excel sheets with similar but not exact layouts, but I noticed when I use proc import, it keeps converting my date of birth (DOB) variable into a character field.

 

Is there a way to specify that this variable needs to be in the date/time format?

 

PROC IMPORT OUT= box.ed_&month.&year. 
		DATAFILE= "C:\Event_Audit_&month.&year..xlsx" 
                DBMS=xlsx REPLACE;
     		SHEET="&month. 20&year."; 
     		GETNAMES=YES;
  		OPTION VALIDVARNAME=V7;/*<------ This will put underscores where there are spaces*/
RUN;
Super User
Posts: 13,008

Re: PROC IMPORT - Changing Variable Type from Character to DateTime

[ Edited ]

Not really when dealing with XLSX data. Proc IMPORT is a guessing procedure and works by examining a few rows of data.

This is a problem because Excel data cells have no data type whatsoever. A column can have text, date, datetime and numeric values in different cells.

 

One recurring theme is to save as CSV files and write (or modify a data step created by proc import) to have the properties needed.

 

You may also have somewhat better luck with the Libname Excel connection to the spreadsheet. That, when things go well and the spreadsheet is resonable well organized, lets you see the sheets in the spreadsheet as data sets. Then you can use data step code to read and save to your desired destination in one step, possibly incorporating changes to values.

 

And a minor side note: If data is either manually entered in the spreadsheet you often have Excel do odd things with some entries. Exporting to CSV will sometime show dates inside quote marks indicating the value was never an Excel date at all but a character string.

 

Super User
Posts: 22,823

Re: PROC IMPORT - Changing Variable Type from Character to DateTime

Your option statement SHOULD not be embedded in the PROC IMPORT. Its an overall SAS option that affects your full program/session and is not related to the PROC IMPORT specifically.
Super User
Posts: 9,551

Re: PROC IMPORT - Changing Variable Type from Character to DateTime

Dump the crappy Excel file format for something reasonable (text-based, like csv or tab-separated), so you can take control of the process. All SAS tools that use Excel files directly (proc import or libname excel/xlsx) have to rely on guessing.

 

With text-based files, you can use proc import to get data step code that you can adapt.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 249

Re: PROC IMPORT - Changing Variable Type from Character to DateTime

how about try to include this few lines? 

proc import ...;
...;
...;
...;
DATAROW=2;
GUESSINGROWS=32767;
MIXED=YES;
USEDATE=YES;
SCANTIME=YES;
...;
run;
Contributor
Posts: 71

Re: PROC IMPORT - Changing Variable Type from Character to DateTime

These extra lines below didn't work when I added it in:

 

GUESSINGROWS=32767;
MIXED=YES;
USEDATE=YES;
SCANTIME=YES;

So I did what others suggested - i converted the CSV and then modified the data step created by proc import.  That fixed my problem!  Took a little more effort, but at least the data is right!

 

thanks everyone!

Super User
Posts: 22,823

Re: PROC IMPORT - Changing Variable Type from Character to DateTime

DBMS=XLSX doesn't support those additional options. I think they work with EXCEL or PCFILES.
Regular Contributor
Posts: 249

Re: PROC IMPORT - Changing Variable Type from Character to DateTime

Thanks @Reeza for pointing out.

Super User
Posts: 13,008

Re: PROC IMPORT - Changing Variable Type from Character to DateTime


einstein wrote:

These extra lines below didn't work when I added it in:

 

GUESSINGROWS=32767;
MIXED=YES;
USEDATE=YES;
SCANTIME=YES;

So I did what others suggested - i converted the CSV and then modified the data step created by proc import.  That fixed my problem!  Took a little more effort, but at least the data is right!

 

thanks everyone!


And if you need to read another file in the same format you only need to change the name of the input file on the INFILE or filename statement and the output data set and the variable properties will all align and not have any problems if you ever combine them.

Plus adding some variable labels is often helpful in the long run.

Ask a Question
Discussion stats
  • 8 replies
  • 970 views
  • 6 likes
  • 5 in conversation