08-24-2017 08:52 AM - edited 08-24-2017 08:54 AM
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.
08-24-2017 09:07 AM
If your model (&sheetname) is called XXX, and you want to have a sheet called XXX_HWA, then just do this:
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.
08-24-2017 09:23 AM
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.
08-24-2017 09:32 AM
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:
%if &nametmp1='OV' %then %let sheetname=&nametmp1||&nametmp2;
08-24-2017 09:40 AM - edited 08-24-2017 09:42 AM
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_
08-24-2017 10:43 AM
@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.
08-24-2017 10:59 AM
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!
08-24-2017 11:14 AM
@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.