Proc import with excel date field

[jerry898969]

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

[SAS83]

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.

[Reeza]

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

[jerry898969]

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.

[Reeza]

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.

[jerry898969]

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

[Reeza]

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

[SAS83]

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.

[jerry898969]

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.

[art297]

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

[jerry898969]

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

[art297]

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