BookmarkSubscribeRSS Feed
jerry898969
Pyrite | Level 9
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
11 REPLIES 11
SAS83
Fluorite | Level 6
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
Super User
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
Pyrite | Level 9
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
Super User
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
Pyrite | Level 9
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
Super User
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
Fluorite | Level 6
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
Pyrite | Level 9
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
Opal | Level 21
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
Pyrite | Level 9
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
Opal | Level 21
Did you try setting the registry to the highest number possible rather than 0?

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 11 replies
  • 40078 views
  • 1 like
  • 4 in conversation