Do loop macro

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Do loop macro

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.

 

accountnumberbalmthendMonthYYYYMM_date
110Jan-17
15Feb-17
13Mar-17
220Jan-17
230Feb-17
210Mar-17
25Apr-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;


Accepted Solutions
Solution
‎08-04-2017 04:57 AM
Super User
Super User
Posts: 7,042

Re: Do loop macro

Posted in reply to PetePatel

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;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,915

Re: Do loop macro

Posted in reply to PetePatel

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?

Occasional Contributor
Posts: 15

Re: Do loop macro

Posted in reply to PetePatel

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

 

accountnumberbalmthend_1balmthend_2balmthend_3balmthend_4
11053.
22030105

 

 

Solution
‎08-04-2017 04:57 AM
Super User
Super User
Posts: 7,042

Re: Do loop macro

Posted in reply to PetePatel

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;
Occasional Contributor
Posts: 15

Re: Do loop macro

Brilliant, thank you!

Super User
Super User
Posts: 7,042

Re: Do loop macro

Posted in reply to PetePatel

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
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 196 views
  • 0 likes
  • 3 in conversation