extract date from datetime with datepart

[zimcom]

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

[ScottBass]

---

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

[zimcom]

@ScottBass 

Thank you, Scott for your help!

[Patrick · Accepted solution]

@zimcom 

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;