topic Re: Proc import with excel date field in SAS Procedures

Proc import with excel date field

jerry898969 — Thu, 31 Mar 2011 20:51:51 GMT

I'm using proc import to import some data from excel. I have two date columns date1 and date2. After I do my import my data table has both columns except date1 is $10 and date2 is a number. Within excel both columns have the same format.
The only thing I can see different is that date2 has more data in the column then date1.

This is my proc import:

PROC IMPORT OUT= dts
DATAFILE= "C:\temp.XLS"
DBMS=EXCEL REPLACE;
SHEET="sheet1";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

Thank you

Re: Proc import with excel date field

SAS83 — Thu, 31 Mar 2011 21:04:31 GMT

If you could provide the first few lines in the excel file, that would be helpful to find the problem. I think date2 is correct as it shows the number, if you can format that to date format (yyddmm.10) you might be able to see the desired date.

Thanks.

Re: Proc import with excel date field

Reeza — Thu, 31 Mar 2011 23:50:31 GMT

Change mixed to yes and make sure your excel file has the date format applied as a format.

Then apply the date format in SAS.

Ie.
data want;
set have;
format date1 date2 date9.;
run;

If you want to convert the date to a date in SAS instead look up the anydttm format(might be missing a few letters there).

HTH,
Reeza Message was edited by: Reeza

Re: Proc import with excel date field

jerry898969 — Fri, 01 Apr 2011 12:41:44 GMT

SAS83 and Reeza,

Thank you both for your replies.

I can not post any of the data i'm working with. It's sensitive data.

I can get around it like this
data temp2;
format date1_dt date9.;
set temp ;
date1_dt = input(date,anydtdte.);
run ;

I'm trying to figure out how to make the import make the table a number like date2 so I don't have to do this step.

Re: Proc import with excel date field

Reeza — Fri, 01 Apr 2011 16:01:48 GMT

A work around is always good.

You don't need to post actual data, but what the data looks like in Excel

ie
date1 date2
March 31, 2001 3-31-2001

or
date1 date2
3/31/01 20110331

There are many ways that dates could be formatted and without 'seeing' it almost impossible to offer any better suggestions.

Re: Proc import with excel date field

jerry898969 — Fri, 01 Apr 2011 16:22:52 GMT

Reeza,

I'm sorry I misunderstood the question. Here is some example data
I don't have a date for date1 till row 52 of my spreadsheet. date2 starts on row 7.

date1
1/1/1940

date2
1/9/2006
1/27/2007

1/11/2009
1/17/2010
1/21/2001
1/7/2001

1/23/2010
1/18/2000

1/1/2005

Re: Proc import with excel date field

Reeza — Fri, 01 Apr 2011 18:00:32 GMT

Its the date not starting until row 52 that's causing the problems then.

One workaround is to save as csv and import using proc import. Get the code from the log and modify the date1 field.

OR

Sort your excel sheet such that the missings are at the end

OR

change your guessing rows settings in registry

and the easiest by far...

read it in and then convert :).

HTH,
Reeza

Re: Proc import with excel date field

SAS83 — Fri, 01 Apr 2011 18:14:50 GMT

Put some dummy dates and then use if statements to make them as null or period.

eg: fill the empty cells with dymmy date which is unique.. like :
01011968 then use

if date1="01011968.d" then date1=.;
I think best option is to sort it as suggested by Reeza. That is the safe option.

Re: Proc import with excel date field

jerry898969 — Fri, 01 Apr 2011 18:29:27 GMT

Reeza,

Thank you for your help. I already have my typeguessrow set to 0 and it still doesn't work.

It is supposed to scan over 16000 rows and decide on the type. The majory have a format of "General" and the date cells have a date format.

Still doesn't work.

If a column is mostly blank I would assume they would make it the type of the data that is in the column.

Thanks again for all your help and suggestions.

Re: Proc import with excel date field

art297 — Fri, 01 Apr 2011 19:05:32 GMT

Jerry,

You missed an important word in Reeza's response, assuming you are on Windows, you have to change the setting in the Registry.

A couple of years ago Datanull posted a solution, on SAS-L, for doing just that. You can find the post at: http://xrl.us/bfa8qi or, in long form,
http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0811b&L=sas-l&D=1&O=A&P=41923

HTH,
Art

Re: Proc import with excel date field

jerry898969 — Fri, 01 Apr 2011 19:36:32 GMT

Art,

Thank you for your response.

When I said "typeguessrow set to 0" in my post I assumed that Reeza knew I was doing it in the registry. I had already made this change in the past. It didn't fix the issue.

thanks

Re: Proc import with excel date field

art297 — Fri, 01 Apr 2011 20:22:13 GMT

Did you try setting the registry to the highest number possible rather than 0?