Macro data to Excel

Reply
Frequent Contributor
Posts: 138

Macro data to Excel

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

Esteemed Advisor
Posts: 5,202

Re: Macro data to Excel

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
Esteemed Advisor
Posts: 7,300

Re: Macro data to Excel

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.

Ask a Question
Discussion stats
  • 2 replies
  • 192 views
  • 0 likes
  • 3 in conversation