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;
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;
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?
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 |
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;
Brilliant, thank you!
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.