BookmarkSubscribeRSS Feed
Ronein
Meteorite | Level 14

Hello

I am using data step to import data from excel.

I have 1 column with date values that are displayed in excel with "Number " format.

(For example : in excel value 42980 represnt  02SEP2017

In sas data step i define the date varaible as a numeric and give a format ddmmyy10.

The problem is that the date appears in SAS is not 02SEP2017!

 

I found a solution to change the format in excel to 3/14/2001 (means  mm/dd/yyyy).

The problem is that my client doesn't want to change the format in excel.

 

Can you offer me a solution ?

2 REPLIES 2
Patrick
Opal | Level 21

@Ronein

Please provide your code and ideally also attach a sample Excel file which illustrates the issue when using your code.

Tom
Super User Tom
Super User

Normally SAS will automatically adjust the dates for you.  Where I have seen it fail is when you have dates and character strings in the same column so that SAS imports the field as character instead of numeric. When that happens it pulls over the internal number that Excel uses for the data as a string of digits.  I have never seen it do that and put the number into a numeric field instead, but perhaps you had a column with mixed dates and other numbers so that SAS decided the column was numeric, but did not think it was a date?

 

You just need to adjust the offset.  SAS uses 01JAN1960 and Excel uses 01JAN1900.  Then -2 in the formula is because SAS starts from zero and Excel starts from one and Excel mistakenly thinks that 1900 was a leap year. (So if you have dates that are before 01MAR1900 the result will be off by one).

 

data test ;
  mydate=42980;
  shouldbe='02SEP2017'd;
  put 'BEFORE: ' mydate= comma7. +1 mydate date9. +1 shouldbe= date9. ;
  mydate = mydate + '01JAN1900'd -2 ;
  put 'AFTER : ' mydate= comma7. +1 mydate date9. +1 shouldbe= date9. ;
  format mydate shouldbe date9. ;
run;
2115  data test ;
2116    mydate=42980;
2117    shouldbe='02SEP2017'd;
2118    put 'BEFORE: ' mydate= comma7. +1 mydate date9. +1 shouldbe= date9. ;
2119    mydate = mydate + '01JAN1900'd -2 ;
2120    put 'AFTER : ' mydate= comma7. +1 mydate date9. +1 shouldbe= date9. ;
2121    format mydate shouldbe date9. ;
2122  run;

BEFORE: mydate=42,980  03SEP2077 shouldbe=02SEP2017
AFTER : mydate=21,064  02SEP2017 shouldbe=02SEP2017
NOTE: The data set WORK.TEST has 1 observations and 2 variables.

 

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
  • 2 replies
  • 1401 views
  • 0 likes
  • 3 in conversation