Merging two datasets (yearly vs. monthly observations) based on ID and date

[kevhuert]

Hi everyone,

This might be a quite trivial problem, but I am completely new at SAS. So I am sorry, if something similar is covered already somewhere and I just didn’t see the connection.

I need to merge two datasets based on ID (wficn) and date in SAS studios. One dataset contains monthly variables and another one yearly. Yearly variables are given for the whole year at the end of the year and I need them to be merged into the data set with monthly observations. This would basically result in having always the exact same value for each of the 12 months per year.

Unfortunately, I have no idea how I can address this problem, so maybe someone can help me out.

Thanks a lot everyone!

[Reeza]

Create a year variable in each data set and then merge by ID and YEAR. If your date values are numeric with a date format that's trivial.

YEAR = year(date);

If you do not have SAS date variables then you should probably first convert them and then do the MERGE.

You can also try applying a YEAR format to each of the variables and specifying the GROUPFORMAT option on the BY statement but I haven't ever tested that option out.

[ballardw]

The key question is going to be are your date and yyyymm variables actually dates or not. If they are SAS date values then this approach would work:

(providing two very small data sets to demonstrate)

data work.monthly;
   input wficn date :date9. somevalue;
   format date ddmmyy10.;
datalines;
1  10Jan2018 1234
1  05Feb2018 4444
1  20Mar2018 5555
1  01Jan2019 1234
1  15Feb2019 4444
1  10Mar2019 5555
;

data work.annual;
input wficn yyyymm :yymmn6. othervalue $;
format yyyymm date9.;
datalines;
1  201812  abc
1  201912  pdq
;
run;

proc sql;
   create table work.want as
   select a.*, b.yyyymm, b.othervalue
   from work.monthly as a
        left join
        work.annual as b
        on year(a.date) = year(b.yyyymm)
   ;
quit;

However if your current variables are not SAS date values, which would be numeric with formats similar to ddmmyy10 applied then you will need to convert YOUR values to dates in this line of code:

on year(a.date) = year(b.yyyymm) 

So, you need to tell us the variable type and your current format to generate that display.

The other variables in your annual data set would be listed in the Select. The a. and b. refer to aliases to address the actual source data sets 1) in short hand and 2) you can't really use libname.dataset.variablename notation in Proc SQL.