Still seems a little confusing. Why does the MAR19 dataset not include the prefix in its name? Shouldn't it be named MONTHLY_BALANCE_MAR19? Also why does the MAR19 dateset not have a value for AMB?
Let's assume that you already have SAS datasets named MONTHLY_BALANCE_SEP18, MONTHLY_BALANCE_OCT18 etc.
Also let's assume that they aren't any other datasets with that same prefix on their member names.
Then you can use generate a dataet list using the : wildcard.
You can use the INDSNAME= option of the SET statement to create a dataset variable with the name of the dataset that contributed the current observation. Then you can convert the date from the dataset name into an actual DATE value.
data want;
length dsname $50 ;
set monthly_balance_: indsname=dsname;
date=input(scan(dsname,-1,'_'),monyy5.);
format date date9.;
monthnum=1+intnx('month','01sep2018'd,date);
run;
proc sort ;
by acct_no monthnum;
run;
Now you have all of the information you need to work the data. If you wanted to flip it up into that WIDE format you asked for (where it will be a lot harder to work with, but perhaps easier to view) then use PROC TRANPOSE.
proc transpose data=want out=wide prefix=month;
by acct_no;
id monthnum;
var amb;
run;
... View more
