Help using Base SAS procedures

Proc import with excel date field

Reply
Super Contributor
Posts: 398

Proc import with excel date field

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
Contributor
Posts: 32

Re: Proc import with excel date field

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.
Super User
Posts: 17,784

Re: Proc import with excel date field

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
Super Contributor
Posts: 398

Re: Proc import with excel date field

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.
Super User
Posts: 17,784

Re: Proc import with excel date field

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.
Super Contributor
Posts: 398

Re: Proc import with excel date field

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
Super User
Posts: 17,784

Re: Proc import with excel date field

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 Smiley Happy.

HTH,
Reeza
Contributor
Posts: 32

Re: Proc import with excel date field

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.
Super Contributor
Posts: 398

Re: Proc import with excel date field

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.
PROC Star
Posts: 7,360

Re: Proc import with excel date field

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
Super Contributor
Posts: 398

Re: Proc import with excel date field

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
PROC Star
Posts: 7,360

Re: Proc import with excel date field

Did you try setting the registry to the highest number possible rather than 0?
Ask a Question
Discussion stats
  • 11 replies
  • 19826 views
  • 0 likes
  • 4 in conversation