BookmarkSubscribeRSS Feed
BCNAV
Quartz | Level 8

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!

 

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

sheet_name="&sheetname._HWA"
Kurt_Bremser
Super User

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.

Reeza
Super User

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. 

BCNAV
Quartz | Level 8

@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;

 

 

 

 

 

 

Kurt_Bremser
Super User

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_

Reeza
Super User

@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. 

 

 

BCNAV
Quartz | Level 8

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!

Reeza
Super User

@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. 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 5422 views
  • 3 likes
  • 4 in conversation