Macro data to Excel

anonymous_user — Wed, 08 Oct 2014 11:12:58 GMT

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

Re: Macro data to Excel

LinusH — Wed, 08 Oct 2014 11:33:56 GMT

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.

Re: Macro data to Excel

art297 — Wed, 08 Oct 2014 12:09:47 GMT

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.

topic Re: Macro data to Excel in SAS Programming

Macro data to Excel

Re: Macro data to Excel

Re: Macro data to Excel