BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Chung-Li
Quartz | Level 8

Hi all,

 

This morning when I was dealing with a data, I faced a probelm related to date transformation.

This data is store in Excel (.xls), and the variable "admission" in Excel looks like:

 

2007/09/21

2003/11/05

.

.

.

 

However, after I import this data into SAS by the following code, variable "admission" change to.

 

code:

PROC IMPORT OUT= WORK.test
DATAFILE= "E:\HardDriveBackUp\BackUp\CL-Wu\AHF\AHF 2015 AHEA
D.xls"
DBMS=EXCEL REPLACE;

RUN;

 

admission:

13409712000

13287369600

.

.

.

 

My question is "how to convert this admission variable into YYMMDD or substract the year?"

I've tried some methods to convert it, but i failed.

 

Last, I show some informations to you that I think it is useful when converting this.

 

1.when this data imported in SAS, it's variable type "number" (know this from proc contents)

2.my code which fail to convert is as follows:

 

DATA test;SET WORK.AHF;
testdate = INPUT(PUT(admission,12.),YYMMDD8.);
FORMAT testdate YYMMDD8.;

1 ACCEPTED SOLUTION

Accepted Solutions
Chung-Li
Quartz | Level 8

Thank you for your suggestions.

My solution is to export this data into other format, such as ".csv".

 

View solution in original post

3 REPLIES 3
mnjtrana
Pyrite | Level 9

SAS assumes 1 Jan 1960 is day 0. Excel assumes 1 Jan 1900 is day 1. You can therefore add or subtract 21 916 from the date depending on whether you are going from Excel to SAS or SAS to Excel. Be careful of negative numbers. SAS accepts the date as being before 1 Jan 1960, Excel does not recognise it.

 

 

You can refer this link:

http://www.sascommunity.org/wiki/Tips:Conversion_from_Excel_Date_to_SAS_Date


Cheers from India!

Manjeet
SASKiwi
PROC Star

Try changing the column type in Excel of admission to DATE to see if that helps.

Chung-Li
Quartz | Level 8

Thank you for your suggestions.

My solution is to export this data into other format, such as ".csv".

 

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