Do loop macro

[PetePatel]

Hello SAS Experts,

I have a long and thin dataset of month end balances for each account which I am trying to shift to short and wide using the code below.

accountnumber	balmthend	MonthYYYYMM_date
1	10	Jan-17
1	5	Feb-17
1	3	Mar-17
2	20	Jan-17
2	30	Feb-17
2	10	Mar-17
2	5	Apr-17

However, if I set max_months as any value over 255 SAS doesn't like it as it breaches the SQL limit I think.

Do you know of a way I can adapt the code below and perhaps play around with a do loop?

Any help would be greatly appreciated.

Thanks,

Pete

%let rep_mth=01Jan2017; *Reporting month;

%let max_months=300; *Set to allow max required months remaining in account lifetimes;

%let fee=35;

*Create dataset with single row per acc at desired obs month (Jan17);

%macro ead_vector;

proc sql;

        create table ead_&rep_mth as

        select %do i = 1 %to &max_months;

                              b&i..BalMthEnd as BalMthEnd_&i,

                  %end;

                  a.*

        from prep2_&rep_mth(where=(MonthYYYYMM_date="&rep_mth."d)) as a

        %do i = 1 %to &max_months;

               left join

               prep2_&rep_mth(where=(MonthYYYYMM_date=intnx('month',"&rep_mth."d,&i,'b'))) as b&i

               on a.accountnumber = b&i..accountnumber

        %end;

        ;

quit;

%mend;

%ead_vector;

[PaigeMiller]

There's no such limit, as far as I know.

To turn long thin data sets into short wide data sets, the first tool I would consider is PROC TRANSPOSE. However ...

You haven't shown us what this short wide output data set should look like, so how could we help you?

[PetePatel]

Sorry, the short and wide datsaset should look like this:

accountnumber	balmthend_1	balmthend_2	balmthend_3	balmthend_4
1	10	5	3	.
2	20	30	10	5

[Tom · Accepted solution]

So it looks like you want to use your macro variables to calculate the name to use for the column?

*Reporting month;
%let rep_mth=01Jan2017;

*Set to allow max required months remaining in account lifetimes; 
%let max_months=300; 

data tall ;
  set have ;
  month = intck('month',"&rep_mth."d,MonthYYYYMM_date)+1;
run;

proc transpose data=tall prefix=balmthend_ out=want (drop=_name_);
  where 1 <= month <= &max_months;
  by accountnumber;
  id month;
  var balmthend;
run;

[PetePatel]

Brilliant, thank you!

[Tom]

What will the resulting dataset look like for your example data?

How is it different from using PROC TRANSPOSE?

data have ;
  length accountnumber balmthend 8 MonthYYYYMM_date $8 ;
  input accountnumber balmthend MonthYYYYMM_date;
cards;
1 10 Jan-17
1 5 Feb-17
1 3 Mar-17
2 20 Jan-17
2 30 Feb-17
2 10 Mar-17
2 5 Apr-17
;
proc transpose data=have out=want (drop=_name_);
  by accountnumber;
  id MonthYYYYMM_date;
  var balmthend;
run;
proc print; run;

Obs    accountnumber    Jan_17    Feb_17    Mar_17    Apr_17

 1           1            10         5         3         .
 2           2            20        30        10         5