Yes, you are right, it was converted wrong, the year should be 2017, that is where I was stopped
usubjid | aestdtc | old_date | new_date |
1001 | 2017-03-02 | 17APR04:15:54:05 | 17APR2004:15:54:05.00 |
1001 | 2017-03-03 | 17APR04:15:56:09 | 17APR2004:15:56:09.00 |
1002 | 2017-03-01 | 17APR05:15:36:48 | 17APR2005:15:36:48.00 |
@zimcom wrote:Yes, you are right, it was converted wrong, the year should be 2017, that is where I was stopped
Well that would be a different problem then. Good luck, unsubscribing from this one...
Your source data is a string in the form YYMONDD:HH:MM:SS
You used informat ANYDTDTM40. to read this string and convert it to a SAS DateTime value (=numerical value; count of seconds since 1/1/1960).
So now by using ANYDTDTM40. SAS will try to read the text string as DDMONYY:HH:MM:SS - and because the text string (your data) also represents a valid datetime in this form SAS will use it and convert it to the number of seconds since 1/1/1960. Now you have a SAS DataTime value and whatever operations and formats you use on this value it's always against the "wrong" count of seconds.
-> your current code gets it wrong when converting the source string into a SAS DateTime value.
Others wrote this already but here again:
What you need to do is first to read the string into a SAS character variable and then "reshuffle" it into a form which a SAS datetime informat can interprete the way you need it. Below a code sample illustrating how this could work (for the date portion of the string).
/* read the excel sheet into SAS */
PROC IMPORT
OUT= source
DATAFILE= "c:\temp\Convert dates.xlsx"
DBMS=xlsx
REPLACE;
SHEET="#LN00046";
GETNAMES=YES;
RUN;
/*18DEC13:11:45:31*/
/*
extract the date string portion from column date_old
and convert it to a SAS date value (number of seconds since 1/1/1960
DT string is in the form: YYMONDD:HH:MM:SS
*/
data want;
set source;
length dt_string $7;
format dt_SASDateValue is8601da.;
dt_string =cats(substrn(date_old,6,2),substrn(date_old,3,3),substrn(date_old,1,2));
dt_SASDateValue =input(dt_string,?? date7.);
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.