ODS tagsets Error

[twildone]

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

[ballardw]

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

[RichardinOz · Accepted solution]

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

[twildone]

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.

[RichardinOz]

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

17        

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

[RichardinOz]

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

[Reeza]

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