BookmarkSubscribeRSS Feed
einstein
Quartz | Level 8

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;
8 REPLIES 8
ballardw
Super User

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.

 

Reeza
Super User
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.
Kurt_Bremser
Super User

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.

Miracle
Barite | Level 11

how about try to include this few lines? 

proc import ...;
...;
...;
...;
DATAROW=2;
GUESSINGROWS=32767;
MIXED=YES;
USEDATE=YES;
SCANTIME=YES;
...;
run;
einstein
Quartz | Level 8

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!

Reeza
Super User
DBMS=XLSX doesn't support those additional options. I think they work with EXCEL or PCFILES.
Miracle
Barite | Level 11

Thanks @Reeza for pointing out.

ballardw
Super User

@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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 5998 views
  • 6 likes
  • 5 in conversation