Qutoted strign error in macro ODS

[Q1983]

Data report1;

Set report0;

Run;

Sample output

ID            investor_class  

1122       FHA

111         FHA

233         Private

234         FHLMC

%Macro Final (investor,sheet_nm);

ODS TAGSETS.ExcelXP                 

   options(sheet_interval='none'                    

           absolute_column_width='8'                

           sheet_name=&sheet_nm.                    

           center_horizontal="no"                   

           Orientation='Landscape'                  

           embedded_titles='No'                

           fittopage="No"                 

           blackandwhite="No"                  

           Embedded_Footnotes='Yes'                 

           autofit_height="Yes");                   

%NoAccountLogic(&final.,&ReportName);               

           PROC REPORT DATA=&final. headskip split='*' wrap nowd      (WHERE = (investor_class = "&investor."))

           style(report)=[background=black cellspacing=10 just=center font_size=11pt font_face="Calibri" bordercolor=black borderwidth=1]       

           style(column)=[background=white font_size=10pt bordercolor=black borderwidth=1]      

           ;         

COLUMNS _all_;

Run;

%Final (FNMA);

%Final (FHLMC);

%Final (Private);

I want to create a 3 tab report based on the investor_class. When I run this I get an error saying there is an expected quote or string error.  Any ideas on what is wrong with the code

&final.

-

22

200

WARNING: Apparent symbolic reference FINAL not resolved.

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, CONNECTION, DICTIONARY.

ERROR 200-322: The symbol is not recognized and will be ignored.

[FreelanceReinh]

Hello @Q1983,

You have referenced a macro variable FINAL, but apparently this macro variable has not been defined previously. There is

• no global macro variable of this name outside the macro
• no macro parameter named FINAL (only INVESTOR and SHEET_NM)
• no local macro variable of this name (e.g. from a %LET statement)

So, what are the values which you want to pass as the first parameter to macro NoAccountLogic and use as input dataset name for PROC REPORT? (Maybe FNMA, FHLMC, etc.?)

This is what the SAS compiler is wondering when you execute macro FINAL (which is totally unrelated to a macro variable of the same name), hence the initial WARNING message. (I hope there is a %MEND statement in your real code.)

[RW9]

Hi,

Why are you writing macros, and such like to output data to multiple sheets?  There is by group functionality in most of SAS including tagsets output, have a look at the documentation:

https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html

Specifically:

Data-Driven Worksheets

By default, a new worksheet is generated for each table. You can override this and create a new worksheet for each procedure, bygroup, or never. The example here creates a new worksheet for each bygroup.

  ods tagsets.excelxp file='multisheet.xls' style=statistical
      options( sheet_interval='bygroup' );

  proc sort data=sashelp.class out=class;
     by age;
  run;

  proc print data=class;
     by age;
  run;

  ods tagsets.excelxp close;