topic Re: Macro Variable Name Used as Excel Sheet Name in SAS Programming

Macro Variable Name Used as Excel Sheet Name

BCNAV — Thu, 24 Aug 2017 12:54:49 GMT

Hey all,

I have a macro that does a series of forecasting. The bits I have an issue with have to do with using the variables being models as sheet names in Excel. It goes like this:

%macro forecast_all (var_list=);
/*
Macro language does not use quotes around the values of macro variables
Macro language %DO loops are limited, and can iterate over a range of numeric values only
*/

%local n f_var sheetname;
%do n=1 %to %sysfunc(countw(&var_list));
	%let f_var=%scan(&var_list,&n);
	data _null_;
    	%let nametmp1=%sysfunc(substr(&f_var,1,2));
		%let nametmp2=%sysfunc(substr(&f_var,7,4));
	%if &nametmp1="OV" %then %let sheetname=&nametmp1||&nametmp2;


forecast code here

ods excel file="R:\Traffic Forecasting\TFM 3.0\Forecast Input Data.xlsx";
			ods excel options(start_at="1,1" sheet_name='&sheetname'+"_HWA" sheet_interval="table");
				proc print data=EGTASK.HF_HWA_OV_ECU_M11 noobs; run;

%end;
%mend;

%forecast_all (var_list=OV_ECU_M11 OV_ECU_M14);

As you can see, I want to create sheet names for Excel based on the variable name, with an append depnding on the forecast. I have tried everything in the line:

ods excel options(start_at="1,1" sheet_name='&sheetname'+"_HWA" sheet_interval="table");

So as you can see, for a variable called OV_ECU_M11, the sheet name for Excel would be OV_M11_HWA. Obviously the + above does not work, I just added it in to show what I wanted to do.

Cheers!

Re: Macro Variable Name Used as Excel Sheet Name

RW9 — Thu, 24 Aug 2017 13:07:12 GMT

Guessing as can't run it (in meeting), but maybe:

sheet_name="&sheetname._HWA"

Re: Macro Variable Name Used as Excel Sheet Name

Kurt_Bremser — Thu, 24 Aug 2017 13:07:20 GMT

If your model (&sheetname) is called XXX, and you want to have a sheet called XXX_HWA, then just do this:

sheet_name="&sheetname._HWA"

The dot symbolises the end of the macro variable name and will not appear in the resolved string. Note that macro variables are not resolved inside single quotes.

Re: Macro Variable Name Used as Excel Sheet Name

Reeza — Thu, 24 Aug 2017 13:23:41 GMT

You're opening ODS EXCEL statement needs to be moved to outside your loop, otherwise you're creating a new file each time? I'm not sure what would happen actually but I wouldn't expect it to work.

Re: Macro Variable Name Used as Excel Sheet Name

BCNAV — Thu, 24 Aug 2017 13:32:07 GMT

@Reeza

There will be about 10 variables, each forecasting with 4 methods. Excel will be poplulated with forecast data from each model as a separate sheet. The ODS seems to work fine so far as it will simply make a new worksheet for each output.

ods graphics on / width=8in;

ods tagsets.sasreport13(id=EGSR) gtitle gfootnote;

Should propbably be moved outside though.

As was posted just before you: sheet_name="&sheetname._HWA" works fine, but the sheet name ends up as _HWA.

It seems the following does not work:

data _null_;

%let nametmp1=%sysfunc(substr(&f_var,1,2));

%let nametmp2=%sysfunc(substr(&f_var,7,4));

%if &nametmp1='OV' %then %let sheetname=&nametmp1||&nametmp2;

Re: Macro Variable Name Used as Excel Sheet Name

Kurt_Bremser — Thu, 24 Aug 2017 13:42:12 GMT

The macro preprocessor knows only one datatype, namely text. Quotes are therefore not needed, unless you want those quotes to be part of the text. There is also no concatenation operator, as concatenation is simply done this way:

%if &nametmp1=OV %then %let sheetname=&nametmp1&nametmp2;

So if &nametmp1 contains OV and &nametmp2 contains XXX, you'll get &sheetname=OVXXX

PS there is also no need for the data _null_

Re: Macro Variable Name Used as Excel Sheet Name

Reeza — Thu, 24 Aug 2017 14:43:15 GMT

@BCNAV just tested it and it appears to not work on my system at least, SAS 9.4 TS1M3.

proc sql noprint;

select distinct age into : age1-
from sashelp.class;
quit;

%let num_ages = &sqlobs.;



%macro loop;

%do i=1 %to &num_ages;

%put &i;

ods excel file='C:\_localdata\temp\test.xlsx';
ods excel options (sheet_name="Age = &&age&i");

proc print data=sashelp.class;
where age=&&age&i;
run;

%end;

%mend;

ods excel close;

%loop;

You're trying to create the file each time, but it's still in use. The ODS EXCEL OPTIONS statement should stay in your loop, but the ODS EXCEL FILE statement should be outside of the loop. Unless you have another loop there to create the sheets.

Re: Macro Variable Name Used as Excel Sheet Name

BCNAV — Thu, 24 Aug 2017 14:59:58 GMT

So this is going well with the replies here...getting there.

By moving the ods excel outside of the loop SAS is adding sheets to the excel file I did not ask for.

They are Directory Information and Library members. Is there a way to suppress this output. Not sure why it is there.

Also, is there a way to refer to multiple Excel files? For example, I would like another separate spreadsheet to house the model estimates

Thanks so much for the help so far everyone!

Re: Macro Variable Name Used as Excel Sheet Name

Reeza — Thu, 24 Aug 2017 15:14:39 GMT

@BCNAV Since you're now facing different issues, you need to post your code. Remember all output is directed to ODS EXCEL, so if you have any procs in that loop that generate output it will go to the Excel file, unless you tell it not to. You can do that using ODS EXCLUDE or ODS SELECT.