SAS Date conversion from text YYYY/MM to DATETIME20. Format

Question

Hi I have a date format in one table in Text 'YYYY/MM'. example 2018/01, 2020/08 etc. I need to join it with another table where the date is in Number type( and DATETIME20 format attached it it) and convert it to month and compare. Is there any way to do it in PROC SQL as the rest of my query is in PROC SQL?

eg. Table 1: Month= 2018/01; Table 2: Date =20.01.2018 10:48:17 . They should be joined in the PROC SQL query. I would also like to calculate difference in Months between these two dates.

Thank you in advance.

Your example value of DATE from TABLE2 is not displayed in the way that DATETIME20. format would display it. Are you sure that the field is a numeric with the DATETIME20. format attached to it? — Tom, Mar 25 '21 at 13:20
Wow..yes..you are correct. I am still new to SAS so trying to figure it out. thank you — Nika M, Mar 25 '21 at 13:48

score 0 · Answer 1 · answered Mar 25 '21 at 13:25

Convert both to the same DATE value. To convert a datetime value to a date use the DATEPART() function. To move to the first day of the month use the INTNX() function with the month interval. To convert a string like '2018/01' to a date you could use INPUT() function with YYMMDD informat by appending '/01'.

proc sql ;
create table want as 
  select * 
  from table1,table2
  where input(cats(table1.month,'/01'),yymmdd10.)=intnx('month',datepart(table2.date),0)
;
quit;

SAS Date conversion from text YYYY/MM to DATETIME20. Format

1 Answers1