Hi,
I have the code below (1.) of which produces the data required and exports to excel when I tell it where to go. The code below that (2.) is where I have used a macro to look at populating the latest blank column from the most updated.
Example - I have the column 51-54 updated with August data, I now need to automatically populate the next 4 colums with Septembers data. Thats what im trying to achieve.
1. Manual Code which stipulates the output location, this works-
%macro freqit(var);
proc freq data=Additional_Dataset;
table &var./out=&var._count missing norow nocol nocum nopercent;
run;
data &var._count(drop=&var.);
set &var._count;
where &var.=1;
percent=percent/100;
run;
%mend;
%freqit(performing);
%freqit(Performing_Arr_Elsewhere);
%freqit(non_performing);
data all_new_asset_type_volume;
set
performing_count
Performing_Arr_Elsewhere_count
non_performing_count;
run;
filename odata dde
"Excel|G:\Mortgages\Optimum\Reporting Challenger\[Co-Op MI Credit Risk.xlsx]Dashboard Data!R5C51:R7C52" notab ;
DATA _NULL_;
SET all_new_asset_type_volume;
FILE odata dlm='09'x;
PUT count percent;
RUN;
/*NEW ASSET TYPE - VALUE and PERCENTAGE*/
%macro outbal(var);
proc means data=Additional_Dataset sum;
var balance_outstanding;
where &var.=1;
output out=&var._balance(drop=_type_ _freq_) sum=;
run;
data &var._balance;
set &var._balance;
percent=balance_outstanding/&total_bal.;
balance_outstanding=balance_outstanding/1000000;
run;
%mend (var);
%outbal(performing);
%outbal(Performing_Arr_Elsewhere);
%outbal(non_performing);
data all_new_asset_type_balance;
set
performing_balance
Performing_Arr_Elsewhere_balance
non_performing_balance;
run;
filename odata dde
"Excel|G:\Mortgages\Optimum\Reporting Challenger\[Co-Op MI Credit Risk.xlsx]Dashboard Data!R5C53:R7C54" notab ;
DATA _NULL_;
SET all_new_asset_type_balance;
FILE odata dlm='09'x;
PUT balance_outstanding percent;
RUN;
2.Macro Code for DDE to replicate as above just take away the manual intervention-
%macro
dataset (mon, mon1, mon2);
data _null_;
call symput('offset',compress(intck('month','01jul2013'd,"&mon."d)+2)); /*Dataset*/
run;
%put offset =&offset.;
%macro freqit(var);
proc freq data=Additional_Dataset;
table &var./out=&var._count missing norow nocol nocum nopercent;
run;
data &var._count(drop=&var.);
set &var._count;
where &var.=1;
percent=percent/100;
run;
%mend;
%freqit(performing);
%freqit(Performing_Arr_Elsewhere);
%freqit(non_performing);
data all_new_asset_type_volume;
set
performing_count
Performing_Arr_Elsewhere_count
non_performing_count;
run;
filename odata dde
"Excel|G:\Mortgages\Optimum\Reporting Challenger\[Co-Op MI Credit Risk.xlsx]Dashboard Data!R5C&offset.:R7C&offset." notab ;
DATA _NULL_;
SET all_new_asset_type_volume;
FILE odata dlm='09'x;
PUT count percent;
RUN;
/*NEW ASSET TYPE - VALUE and PERCENTAGE*/
%macro outbal(var);
proc means data=Additional_Dataset sum;
var balance_outstanding;
where &var.=1;
output out=&var._balance(drop=_type_ _freq_) sum=;
run;
data &var._balance;
set &var._balance;
percent=balance_outstanding/&total_bal.;
balance_outstanding=balance_outstanding/1000000;
run;
%mend (var);
%outbal(performing);
%outbal(Performing_Arr_Elsewhere);
%outbal(non_performing);
data all_new_asset_type_balance;
set
performing_balance
Performing_Arr_Elsewhere_balance
non_performing_balance;
run;
filename odata dde
"Excel|G:\Mortgages\Optimum\Reporting Challenger\[Co-Op MI Credit Risk.xlsx]Dashboard Data!R5C&offset.:R7C&offset" notab ;
DATA _NULL_;
SET all_new_asset_type_balance;
FILE odata dlm='09'x;
PUT balance_outstanding percent;
RUN;
%mend;
%dataset (&d1.,&d2.,&d3.);
Tip: try to scale down your code as much as possible. You are likely to have more people helping you if you can avoid us test nested macros etc.
I think you want a different calculation for offset. Possibly something like:
%let mon=01sep2013;
data _null_;
call symput('offset',compress(intck('month','01jul2013'd,"&mon."d)*4+47)); /*Dataset*/
run;
%put &offset.;
That would always give you the correct starting column based on &mon.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.