ODS tagsets Error

Accepted Solution Solved
Reply
Regular Contributor
Posts: 222
Accepted Solution

ODS tagsets Error


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


Accepted Solutions
Solution
‎01-03-2014 04:09 PM
Super Contributor
Posts: 644

Re: ODS tagsets Error

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

View solution in original post


All Replies
Super User
Posts: 10,500

Re: ODS tagsets Error

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

Solution
‎01-03-2014 04:09 PM
Super Contributor
Posts: 644

Re: ODS tagsets Error

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

Regular Contributor
Posts: 222

Re: ODS tagsets Error

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.

Super Contributor
Posts: 644

Re: ODS tagsets Error

@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

Super Contributor
Posts: 644

Re: ODS tagsets Error

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

Super User
Posts: 17,829

Re: ODS tagsets Error

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 269 views
  • 3 likes
  • 4 in conversation