topic Re: SAS left join datasets with different date formats in SAS Programming

SAS left join datasets with different date formats

jeremy4 — Thu, 01 Aug 2019 10:01:44 GMT

Hi,

I would like to left join two datasets onto another dataset (so three datasets in total) but each dataset has a different date format - can someone please tell me the best way to match them together (I have written code below but need to fill in the code highlighted in red to match the three datasets)? For the cohort_mth variable, which has a DATE9 format, this would be matched based on month e.g. any date in a month such as 01JAN2019 or 22JAN2019 would be recognised as JAN2019, so that it could be matched to JAN2019 (mth_end variable) and 201901 (obs_month variable)? Thanks!

ar.dataset1

ar.dataset2

ar.dataset3

Code so far:

proc sql;

create table as
select a.mth_end,
a.account_id,

b.letter,

c.score

from ar.dataset1 as a

left join ar.dataset2 as b

on ..................................... /*What code to combine the three datasets by month/year?*/

group by mth_end, account_id, letter, score;

quit;

Re: SAS left join datasets with different date formats

PaigeMiller — Thu, 01 Aug 2019 10:39:33 GMT

Didn't we already discuss this and propose a solution in your earlier threat at: https://communities.sas.com/t5/SAS-Programming/Matching-dates/m-p/576652#M163255

Yes, we did. The answer there works here. Convert the date variable in each data set to an actual SAS date value containing the 1st of each month, and then merge.

Re: SAS left join datasets with different date formats

jeremy4 — Thu, 01 Aug 2019 10:44:44 GMT

Would the code be:

proc sql;

create table as
select a.mth_end,
a.account_id,

b.letter,

c.score

from ar.dataset1 as a

left join ar.dataset2 as b

on intnx('month',a.mth_end,0,'b') = intnx('month',b.cohort_mth,0,'b') AND intnx('month',a.mth_end,0,'b') = intnx('month',c.obs_month,0,'b')

group by mth_end, account_id, letter, score;

quit;

Re: SAS left join datasets with different date formats

PaigeMiller — Thu, 01 Aug 2019 12:16:32 GMT

Any time you want to match dates, they must be SAS date values, which are integers, for example today (August 1, 2019) is 21762, this is the number of days since January 1, 1960. (That's how SAS does things). So you would have to convert the dataset3 variable to an actual SAS date value. 201801 is not a SAS date value (this is done via the INPUT function, using the proper informat which is YYMMN6.)

proc sql;

   create table as
   select a.mth_end,
             a.account_id,
             b.letter,
             c.score
   from ar.dataset1 as a
   left join ar.dataset2 as b 
    on intnx('month',a.mth_end,0,'b') = intnx('month',b.cohort_mth,0,'b')
    left join ar.dataset3 as c
    on intnx('month',a.mth_end,0,'b') = input(c.obs_month,yymmn6.)
   group by mth_end, account_id, letter, score;
quit;