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