topic Re: Importing a date in SAS Programming

Importing a date

buckeyefisher — Tue, 10 Feb 2015 14:50:38 GMT

Although my excel has a field in date format, SAS is importing it in Character format.

For example date in excel is 12/31/2008 but its imported in SAS as 39813.

So I tried following code to correct it but not getting correct date.

PROC SQL noprint;

CREATE TABLE want AS

SELECT *, input('Established Date'n,ANYDTDTE22.) as new_date format=mmddyy12.

FROM input;

QUIT;

Can someone help?

Thanks

Re: Importing a date

Loko — Tue, 10 Feb 2015 15:08:22 GMT

Hello,

If you post the log from the imported step it may help noticing how the column has been guessed by SAS

Re: Importing a date

HenryFeldman — Tue, 10 Feb 2015 15:12:44 GMT

How are you reading in the excel spreadsheet? The code that reads spreadsheets scans the column to determine the field type. The implication is that it is seeing mixed numeric (formatted as dates) and character data. The 39000 number is the number of days since 1/1/1900. If you don't have many fields , I can give you an equation to get that Ito a SAS date. Henry

Re: Importing a date

buckeyefisher — Tue, 10 Feb 2015 15:12:59 GMT

log doesn't give that information. Here is the log

PROC SQL noprint;

107 CREATE TABLE want AS

108 SELECT *, input('Established Date'n,ANYDTDTE22.) as new_date format=mmddyy12.

109 FROM input;

NOTE: Table WORK.WANT created, with 4263 rows and 10 columns.

110 QUIT;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.05 seconds

cpu time 0.04 seconds

Re: Importing a date

buckeyefisher — Tue, 10 Feb 2015 15:17:38 GMT

I am wondering why SAS is not recognizing the date even when I set the column property in excel as date format.

I am importing excel using this

proc import datafile='input.xls'

dbms=xls out=output replace;

sheet="Profile";

run;

I have 4000 rows, so can you please give me code?

Re: Importing a date

HenryFeldman — Tue, 10 Feb 2015 15:51:06 GMT

the proc import dbms=xls code looks at the indvidual cell contents. If a column has both numeric and character cells then it will be a character field.

Excel is # of days since 1/1/1900. SAS is # of days since 1/1/1970. So if you convert the character string to a numeric and apply a date format, the number will be 70 years in the future. The following equation needs to be applied to each date variable.

numdate = input(chardate,best.) - 21915;

(BTW, if your dates still 4 years in the past, then the spreadsheet has a 1904 base date and you'll need to ADD back in 1462.)

Here is my entire test program:

data;

chardate = "39000";

numdate = input(chardate,best.) - 21915;

format numdate date.;

put numdate;

run;

Hope that is helpful

Henry

(developer of the sas/acccess for pc files dbms=xls code)

Re: Importing a date

Reeza — Wed, 11 Feb 2015 17:04:15 GMT

1960 instead of 1970?

Re: Importing a date

HenryFeldman — Wed, 11 Feb 2015 17:09:40 GMT

Yup 1960. But the correction factor is right. about 60 * 365

Re: Importing a date

ballardw — Wed, 11 Feb 2015 17:10:37 GMT

Is in coming in numeric or character? If character using the input method isn't likely to work as 39813 doesn't match any of the likely SAS date informats. And if you happen to have one that does look like an acceptable date it likely will convert incorrectly due to the offset used by Micro$oft.

Re: Importing a date

HenryFeldman — Wed, 11 Feb 2015 17:15:47 GMT

The 39813 is the underlying value in excel spreadsheet cell. (Try entering a date value and then changing the format to number.) It is the number of days since 1/1/1900.

Re: Importing a date

PoornimaRavishankar — Wed, 11 Feb 2015 18:23:36 GMT

It looks like your field is NOT being imported the way it is being 'dispayed' in excel because if it did, you'd be seeing "12/31/2008" in SAS when you display it without formatting.

Next, if you did use ANYDTDTE22. on this number -39813, I would think you'd be seeing a nice little '.' (Tested)

1. HenryFeldman's way

2. If you can, convert your excel to csv. The csv will be created with the dates actually being stored as "12/31/2008". You can then simply use the input function on the field within SAS and it will store it as a SAS date(from 1/1/1960).

Re: Importing a date

ballardw — Wed, 11 Feb 2015 19:03:42 GMT

Something else to consider, especially if more than one person has worked on the spreadsheet. You may have some cells in a column that are not "formatted" the same way. And you haven't said how the data was imported.

I have some data I get from folks that use Excel that I have to reformat whole columns, save as CSV and then read that to have enough control to get the correct values for dates.