topic Re: ODS tagsets Error in SAS Programming

ODS tagsets Error

twildone — Fri, 03 Jan 2014 14:46:58 GMT

Hi, I keep getting an error message when I include references in the ods file statement. I would like to something similiar to the first ods tagsets file statement so that a report would be generated similiar to the second ods tagsets file name. Thanks in advance.

%LET begperiod = intnx('Year.4',intnx('Month',today(),-1,'E'),0,'B');
%LET endperiod = put(intnx('Month',today(),-1,'e'),monyy7.);

ods tagsets.excelXP file="U:\2013-14 Income Reports\Incomerpt&begperiod up to end of &endperiod.xls";

ods tagsets.excelXP file="U:\2013-14 Income Reports\Incomerpt2013 up to end of Dec2013.xls";

Re: ODS tagsets Error

ballardw — Fri, 03 Jan 2014 15:55:25 GMT

Please post errors generated by code when asking questions related to errors.

Re: ODS tagsets Error

RichardinOz — Fri, 03 Jan 2014 21:09:31 GMT

I would not expect SAS functions to be resolved in an ods statement. Instead use %sysfunc in the macro variable definitions:

%LET begperiod = %sysfunc(intnx(Year.4, %sysfunc(intnx(Month, %sysfunc(today()),-1,E)),0,B));
%LET endperiod = %sysfunc(putN(%sysfunc(intnx(Month, %sysfunc(today()),-1,e)),monyy7.));

ods tagsets.excelXP file="U:\2013-14 Income Reports\Incomerpt&begperiod up to end of &endperiod.xls";

Alternately do the calculations in a data _Null_ step :

data _Null_ ;

begperiod = intnx('Year.4',intnx('Month',today(),-1,'E'),0,'B');
endperiod = put(intnx('Month',today(),-1,'e'),monyy7.);

Call Symput ('begperiod', begperiod) ; /* leading spaces */

Call Symput ('endperiod', endperiod) ;

run ;

%Let begperiod = &begperiod ; /* to manage trimming */

The code is going to deliver a SAS date numeral for &begperiod - is that what you want?

(Untested - check for missing or misplaced parentheses, use putn instead of put)

Richard temporarily back in Oz

Re: ODS tagsets Error

Reeza — Fri, 03 Jan 2014 23:28:41 GMT

You'll also need periods after the end of the macro variables in the file= so the macro variables will resolve properly.

Re: ODS tagsets Error

twildone — Thu, 09 Jan 2014 02:01:21 GMT

Hi Richard,

I tried both of your suggestions. When I tried to use the %sysfunc in the macro variable definitions, I would get an error message indicating that one of the arguments is not a number.

However when I tried your suggestion of doing the calculations in a data _Null_ step and Rezza suggestion of including periods after the end of the macro variables in the file= so the macro variables will resolve properly, it worked fine. Thanks once again for your much appreciated help and suggestions.

Re: ODS tagsets Error

RichardinOz — Wed, 15 Jan 2014 09:15:28 GMT

@twildone

Sorry about the lag in replying. I do not know why you are getting the error you reported. After adding the extra period in the one place, before the xls (Duh! thanks Reeza) and using a %Put before the ods statement for the purpose of demonstration this is what I got

15 %LET begperiod = %sysfunc(intnx(Year.4, %sysfunc(intnx(Month,

15 ! %sysfunc(today()),-1,E)),0,B));

16 %LET endperiod = %sysfunc(putN(%sysfunc(intnx(Month, %sysfunc(today()),-1,e)),monyy7.));

18 %Put ods tagsets.excelXP file="U:\2013-14 Income Reports\Incomerpt&begperiod up to end of

18 ! &endperiod..xls";

ods tagsets.excelXP file="U:\2013-14 Income Reports\Incomerpt19449 up to end of DEC2013.xls"

[Log from EG 5.1 with server SAS 9.3 - not that I would expect any different from an earlier version]

However, the Data _Null_ approach is perhaps the best way to go - easier to follow, easier to debug, no %sysfuncing!

Richard in NZ

Re: ODS tagsets Error

RichardinOz — Wed, 15 Jan 2014 09:21:35 GMT

Just checking on your original question there is also a missing Year() function to convert the SAS date in &begperiod to the year value.

Richard