BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
kevsma
Quartz | Level 8

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!

1 ACCEPTED SOLUTION

Accepted Solutions
kevsma
Quartz | Level 8

@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!!

View solution in original post

6 REPLIES 6
PaigeMiller
Diamond | Level 26

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?

--
Paige Miller
kevsma
Quartz | Level 8

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
Super User

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
Quartz | Level 8

@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
Super User

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
Quartz | Level 8
no worries, I got it worked out, turns out to be a space accidentally added to a format variable. thanks!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 756 views
  • 2 likes
  • 4 in conversation