DATA Step, Macro, Functions and more

Importing a date

Reply
Contributor
Posts: 63

Importing a date

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

Super Contributor
Posts: 308

Re: Importing a date

Posted in reply to buckeyefisher

Hello,

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

Contributor
Posts: 63

Re: Importing a date

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

SAS Employee
Posts: 4

Re: Importing a date

Posted in reply to buckeyefisher

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

Contributor
Posts: 63

Re: Importing a date

Posted in reply to HenryFeldman

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?

SAS Employee
Posts: 4

Re: Importing a date

Posted in reply to buckeyefisher

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)

Super User
Posts: 19,870

Re: Importing a date

Posted in reply to HenryFeldman

1960 instead of 1970?

SAS Employee
Posts: 4

Re: Importing a date

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

Super User
Posts: 11,343

Re: Importing a date

Posted in reply to buckeyefisher

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.

SAS Employee
Posts: 4

Re: Importing a date

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.

Contributor
Posts: 53

Re: Importing a date

Posted in reply to buckeyefisher

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).

Super User
Posts: 11,343

Re: Importing a date

Posted in reply to buckeyefisher

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.

Ask a Question
Discussion stats
  • 11 replies
  • 1469 views
  • 1 like
  • 6 in conversation