topic Using a macro field within a proc report to name a tab in excel output in SAS Programming

Using a macro field within a proc report to name a tab in excel output

LouiseH2010 — Thu, 18 Oct 2018 15:29:44 GMT

Hi,

I am trying create an excel report where each tab contains several reports where a new tab is created depening on the value within a macro field. The report itself is a macro that is called several times per tab and the call contains the value that names the tab.

Below is a simplified version of the code:

ods tagsets.excelxp file= "&outpath\&jn - &title Rolling 13 Months &sysdate..xls"

options(sheet_interval = "none"

embedded_titles = "yes"

embedded_footnotes = "yes"

skip_space = '0,0,0,0,0');

%macro all_report(product,characteristic,title3,char_format);

%macro dummy;

%mend dummy;

ods tagsets.excelxp options(sheet_interval = "none"

sheet_name="&Product."

);

Proc report data=SAS_DATA.rfv_storecards_all_months_&product. nowd;

title1 justify=left " ";

title2 justify=left " ";

title3 justify=left "&title3.";

Columns customergroup &characteristic._&dt_month3_year3. &characteristic._&dt_month2_year2. &characteristic._&dt_month1_year1.

&characteristic._&dt_month0_year0.;

Define customergroup / order order=internal "Customer Group";

Define &characteristic._&dt_month3_year3. / group "&dt_month3_year3." style(column)={tagattr=&char_format.};

Define &characteristic._&dt_month2_year2. / group "&dt_month2_year2." style(column)={tagattr=&char_format.};

Define &characteristic._&dt_month1_year1. / group "&dt_month1_year1." style(column)={tagattr=&char_format.};

Define &characteristic._&dt_month0_year0. / group "&dt_month0_year0." style(column)={tagattr=&char_format.};

run;

%mend;

%all_report(retailer1,customer,Customers,'format:#,##');

%all_report(retailer1,netsales122,Net Sales,'format:£#,##0,00');

%all_report(retailer1,trans122,Transactions,'format:#,##');

%all_report(retailer1,ATV,ATV,'format:£#,##0.00');

%all_report(retailer1,ASS,ASS,'format:£#,##0.00');

%all_report(retailer2,customer,Customers,'format:#,##');

%all_report(retailer2,netsales122,Net Sales,'format:£#,##0,00');

%all_report(retailer2,trans122,Transactions,'format:#,##');

%all_report(retailer2,ATV,ATV,'format:£#,##0.00');

%all_report(retailer2,ASS,ASS,'format:£#,##0.00');

ods _all_ close;

What I want to end up with is a tab called 'retailer1' with 5 reports on it and a second tab called 'retailer2' with 5 reports on it. The variable 'product' in the call statement holds the name for the tab. I have tried several places to put the sheet-Interval and sheet_name options but no joy yet.

Any ideas?

many thanks

Re: Using a macro field within a proc report to name a tab in excel output

Reeza — Thu, 18 Oct 2018 15:33:32 GMT

Try setting sheet_label to blank as well and change the sheet_interval to Now in the macro.

ods tagsets.excelxp options(sheet_interval = "now"

sheet_name="&Product."

);

And you really shouldn't nest macro's within each other, that's a recipe for problems.

Re: Using a macro field within a proc report to name a tab in excel output

LouiseH2010 — Fri, 19 Oct 2018 09:52:50 GMT

Hiya,

Thank you so much for your response - I have tried this in several different places and no luck. I either get 10 tabs with 1 report per tab or 1 tab for the first retailer and all 5 reports and nothing for the second retailer.

By nested macro's are you referring to the dummy macro as this is purely to get the colours in the code to be present to make it easier to read.

Thanks

Re: Using a macro field within a proc report to name a tab in excel output

Reeza — Fri, 19 Oct 2018 14:52:57 GMT

Check your version of the tagsets, it shows up in the log. It should be 1.131 or 2015 - something like that.

Re: Using a macro field within a proc report to name a tab in excel output

LouiseH2010 — Fri, 19 Oct 2018 14:57:15 GMT

It's v1.130

Re: Using a macro field within a proc report to name a tab in excel output

Reeza — Fri, 19 Oct 2018 14:59:27 GMT

Add another parameter to your macro call, that controls the sheet interval.

When you want to start a new sheet have it called now. At the bottom of the macro reset it back to none so that you always have to specify it at the top of the macro.

Re: Using a macro field within a proc report to name a tab in excel output

Reeza — Fri, 19 Oct 2018 15:00:37 GMT

It's out of date, update it and try again.

https://support.sas.com/rnd/base/ods/odsmarkup/index.html