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: Call for Content

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!

Submit your idea!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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