set up macro for proc report

[kevsma]

Hello folks, I have a large volume of tables to process and basically I have the main body of the code ready, but I don't know how to set up a macro to let the whole part of code run by each center. Below is the beginning of my code:

*Macros;
%LET Regctr = WS;
%LET fy = 2022-2023;
%LET RCFULL = Full name of the center;

*Import excel file for data source;
proc import out=&Regctr. datafile= "&inpath\&Regctr..xlsx" 
	 dbms=xlsx replace;
	 sheet="_&Regctr";
     getnames=yes;
run;

*SEPARATE CONTRACTS;
DATA &Regctr.; SET &Regctr.;
	UCI_0=INPUT(UCI,12.);
RUN;

DATA &Regctr.; SET &Regctr.;
	WHERE UCI_0 NE . ; RUN;
RUN;

Main bulk of the code continue to the very end ... and then at the proc report step below:

*---------------------------------SAS output, complete version, separate by tabs------------------------;
ODS EXCEL FILE='Tables.xlsx' 
 OPTIONS(SHEET_INTERVAL='none' SHEET_NAME='table1' EMBEDDED_TITLES='yes'
 TAB_COLOR='green' START_AT='2,4' FROZENHEADERS='yes');

OPTIONS MISSING='0';
ODS ESCAPECHAR='^';

title1 j=center bold font=calibri "my title";
title2 j=center bold font=calibri "some other title";
title3 j=right italic font=calibri "Fiscal Year &fy";
title4 j=right italic font=calibri "Page 1 of 1";
title5 j=left italic font=calibri "&RCFull";
title6 j=left bold font=calibri "All Ages";

PROC REPORT DATA=tbl1 NOWD HEADSKIP HEADLINE SPACING=1 
 	STYLE(report)={width=100%}
	STYLE(column)=[background=white fontstyle=roman fontsize=3 fontweight=medium  
				  fontfamily='calibri']
	STYLE(header)=[background=white fontstyle=roman fontsize=3 fontweight=bold 
				  fontfamily='calibri']
	STYLE(lines)=[background=white fontstyle=roman fontweight=medium  
				  fontfamily='calibri' color=black]
;

...

As you can see, I have a total of 21 centers that I need to run the same code over again, and  below is what i am trying to resolve:

1. how could I set up the macro so that it grabs the two-letter abbreviations (in the above example, WS needs to be changed to another two letter abbreviation such as AB, CB, EB, etc when WS part has finished running) and then also change the full name accordingly?
2. by the time the code runs towards the end, i also need the macro to save the tables using the two-letter abbreviation, for example, change the current part

ODS EXCEL FILE='Tables.xlsx'

to something like ODS EXCEL FILE='WS.xlsx' and go on...

I feel this might be something easy to accomplish but i can't figure out a proper way, any ideas? Thank you so much!

[PaigeMiller]

If you can accept all output in a single Excel file, such as tables.xlsx, with TABs labelled by the two letter abbreviation, the you can do all of the reports with a single PROC REPORT and a BY statement in PROC REPORT, and the proper ODS EXCEL options.

If you absolutely have to have them all in separate Excel files, then please explain how the programmer will know the next two letter abbreviation after WS. Where is that information stored?

[kevsma]

Hello @PaigeMiller  I want each center's file to be a separate excel file, instead of having them all in one excel file. can I store the 2-letter abbreviation and their corresponding full name in excel file saved on local drive, say, saved it the same path as &inpath. I can also import it into a SAS proc format file like below: 

PROC FORMAT;
Value  $CENTER2     
                    "WC" = "NAME1"        
                    "BC" = "NAME2"        
                    "CB" = "NAME3"       
                    "GT" = "NAME4"     
                    ...
;     
run;      

Would this work?

...

[Reeza]

Create a table with the 21 list of codes and names.

Take the full code and wrap it in a macro
e.g.

%macro run_report (code = , name = , fy=);
code ....
%mend;

Test it with a few manual calls.

options mprint mlogic symbolgen;
%run_report (code = WS, name = Station1, fy = 2022-2023);
%run_report(code= AB, name=Random, fy=2021-2022);

Get this working first.

UCLA introductory tutorial on macro variables and macros

https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro

This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...

[kevsma · Accepted solution]

@Reeza Thanks for the reply, I tried to plug in the example you showed here but SAS spits out errors, my beginning part looks like this:

%macro run_report (Regctr = , RCFULL = );

*main part of the code begins;
  *Import excel file first;
  proc import out=&Regctr. datafile= "&inpath\&Regctr..xlsx" 
	 dbms=xlsx replace;
	 sheet="_&Regctr";
     getnames=yes;
  run;

and the end looks like this:

...main code...
ODS EXCEL CLOSE;

%mend;


%run_report(Regctr = CV, rcfull = full name 1, fy = 2022-2023);
%run_report(Regctr = BV, rcfull = full name 2, fy = 2022-2023);

anything i may be missing? thanks!!

[ballardw]

If you don't show the errors how do you expect us to address them?

With macros that means setting OPTIONS MPRINT; before running the macro so the errors appear near the code that generates them.

Then copy that generated code and the errors from the log and paste into a text box on the forum.

[kevsma]

no worries, I got it worked out, turns out to be a space accidentally added to a format variable. thanks!