topic Re: Do loop macro in SAS Programming

Do loop macro

PetePatel — Thu, 03 Aug 2017 16:56:55 GMT

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;

Re: Do loop macro

PaigeMiller — Thu, 03 Aug 2017 16:59:43 GMT

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?

Re: Do loop macro

PetePatel — Thu, 03 Aug 2017 17:03:21 GMT

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

Re: Do loop macro

Tom — Thu, 03 Aug 2017 17:04:46 GMT

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

Re: Do loop macro

Tom — Thu, 03 Aug 2017 17:42:28 GMT

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;

Re: Do loop macro

PetePatel — Fri, 04 Aug 2017 08:58:16 GMT

Brilliant, thank you!