Macro for mapping balances

[Fabeeha]

Hi All,

I have 3 sas datasets Mar19,Apr19 and May19. My task is to map last 6 months average monthly balances from 6 tables to my datasets. Also I want these mapped variables to be named as month1,month2,month3,month4,month5 and month6. I have written the below code to do the needful.However,I am stuck at naming the variables as month1 to month6 so that when I append these datasets I don't have to rename my columns. My code is giving me variable names as

For Mar19 base - month1,month2,month3,month4,month5,month6

For Apr19 base - month2,month3,month4,month5,month6,month7(expected output month1,month2,month3,month4,month5,month6)

For May19 base- month3,month4,month5,month6,month7,month8(expected output month1,month2,month3,month4,month5,month6)

Can somebody please suggest me the solution.Thanks! 

%let base1=Mar19;
%let base2=Apr19;
%let base3=May19;

%let month1=Sep18;
%let month2=Oct18;
%let month3=Nov18;
%let month4=Dec18;
%let month5=Jan19;
%let month6=Feb19;
%let month7=Mar19;
%let month8=Apr19;
%let month9=May19;

%macro map;
%do i=1 %to 3; 
%do j=&i %to %eval(&i+5);

proc sql;
create table &&base&i
as
select a.*,b.AMB as month&j from &&base&i a 
left join monthly_balance_&&month&j b
on a.acct_no=b.acct_no;
quit;
%end;
%end;
%mend;
option mprint mlogic nosymbolgen;
%map;

[Reeza]

Redesign your process to be a long file with the months. Then you can numerically order them easily. To get your wide format at the end, transpose the data instead of using a SQL query. That will be a fully dynamic solution and will scale as your months grow.

[Fabeeha]

Thanks but can you please explain it by giving me an example.Also at this stage of data pulling I don't intend to use proc transpose. 

[Tom]

It is really not clear what your data looks like.  Can you just post a simple data step to create the example data?

It sounds a little like you have data like:

data MAR19;
  input month1-month6 ;
cards;
1 2 3 4 5 6
7 8 9 10 11 12
;

Then explain what that means.  What is the MONTH1 variable? Waht is the MONTH2 variable, etc.

What do the different observations mean?  Is there a variable (or combination of variables) that unique identify the observations in the MAR19 dataset?

Does the name of the file MAR19 imply some type of DATE value?  If so what date?  Is it the 19th day of March? If so what year?

Is the value of this date also in the data or do you need to convert the name of dataset into data in order to use the data?

[Fabeeha]

Mar19 is basically dataset name  where all the mapping needs to be done. Mar19 depicts Mar2019(month and year).It is just the way dataset has been named.There is no date field in the given datasets.Below is the sample data for better understanding.

/* main data*/
data mar19;
input acct_no $5.;
cards;
10001
25001
54002
10000
;
run;

/* monthly_balance datasets from sep18(month and year sep ,2018) to feb19 in the same format */
data monthly_balance_sep18;
input acct_no $5. amb best32.;
cards;
10001 15000
25001 35000
10000 11152
;
run;

/* desired output dataset should look like */
/* month1 is amb of sep18 */
/* month2 is amb of oct18 */
/* month3 is amb of nov18 */
/* month4 is amb of dec18 */
/* month5 is amb of jan19 */
/* month6 is amb of feb19 */


cards month1 month2 month3 month4 month5 month6
10001 15000  500    100    6300   1200   3600
25001 35000  1111   10      7000   1000   1200 
54002             1000   5000 1000   3600   9200
10000 11152   400    20000 8000 10000

[Tom]

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;