Yes, this seems to be default setup in some industries to have same data split up by years and months. What it effectively means is more storage overal, much more complicated messy programming and more macro code.
What I will say, as always, is put all your data together, then process the data. It is far simpler, you don't need to do macro, or do loops, or append data etc.
So, step 1, put all month data together with a new variable of month:
data month_data;
set tablename_: indsname=tmp; /* Create an actual date so further processing is easier */ month=input(cats(tranwrd(tmp,"tablename_",""),"01"),yymmdd8.); format month date9.;
run;
This is just based on what you posted here. Now you can join rand_cust onto this data (using the sql you presented).
What we have then is one dataset, that can be filtered very simply and efficiently by using the month variable, and by using date functions like intck, rather than trying to work out if its the end of the year etc. So for example to get data from 201604 to 201605, you could simply do:
data want;
set month_data; where input(cats("201605","01"),yymmdd8.) <= month <= input(cats("201606","01"),yymmdd8.); run;
No need for many datasets, no need to append, mo messy macro code which will fall over every other run, just simple Base SAS programming on data.
... View more
