BookmarkSubscribeRSS Feed

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.);

2 REPLIES 2
LinusH
Tourmaline | Level 20

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.

Data never sleeps
art297
Opal | Level 21

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: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 598 views
  • 0 likes
  • 3 in conversation