BookmarkSubscribeRSS Feed
buckeyefisher
Obsidian | Level 7

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

11 REPLIES 11
Loko
Barite | Level 11

Hello,

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

buckeyefisher
Obsidian | Level 7

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

HenryFeldman
SAS Employee

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

buckeyefisher
Obsidian | Level 7

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?

HenryFeldman
SAS Employee

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)

Reeza
Super User

1960 instead of 1970?

HenryFeldman
SAS Employee

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

ballardw
Super User

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.

HenryFeldman
SAS Employee

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.

PoornimaRavishankar
Quartz | Level 8

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

ballardw
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 10270 views
  • 2 likes
  • 6 in conversation