help w macro in combo w proc report

mmurph15 — Fri, 22 Oct 2021 16:09:04 GMT

I have 3 proc report statements that I need to run 25 times to get my desired result (one time for each "grantee"). I know there is a simple way to do this with a macro(?), but my experience with macros is limited and I'm struggling.

I want to run the following reports 25 times (1 for each value of the variable "grantee") - i.e., run the reports through once on a subset of data where grantee =grantee1, then run it through again where grantee=grantee2, etc. I have it set up in the code below with a %let statement - this works when i type the grantee name in, one by one, and run it 25 times. I'm sure there's a faster way where it automatically runs through each value of grantee without having to change the %let statement. Any advice? (I feel like i'm missing something obvious and simple - thank you in advance!)

**********************;

%let Q=2021 Q2; *MUST CHANGE EVERY QUARTER - reporting quarter;

%let D=11/30/2021; *MUST CHANGE EVERY QUARTER - end of the current quarter;

%let G=GRANTEE1; *would need to run for each grantee, 25 total - how to omit this statement and instead use a macro ?;

**********************;

*incorporate a macro so these 7 reports will run 25 times - once per value of grantee - without typing in the values of the grantee;

%macro granteereport (??);

*******************************************************************************************;

*REPORT 1 of 7 - Strategy 1 Activities, Training, Outcomes;

ods rtf file = "P:\Shared\Reports\&G._&Q..rtf" startpage=no;

Title1 "2021-2026 &G Report";

Title2 "6/1/2021 - &D";

**1a.;

proc report data=convert;

where grantee="&G" and FW_Idname ne '';

column ("Site Level Activities, Training and Technical Assistance" FW_IDname ("Key Activities" nFWA_B01-nFWA_B03) ("Training and Technical Assistance - Year 1" ("Nutrition" nFWT_N11-nFWT_N14 ) ("Physical Activity" nFWT_P11-nFWT_P14) ));

define FW_idname / group display "Worksite ID|Name";

define nFWA_B01 / analysis sum "Recruited, ID'd Partners" center format=check.;

define nFWA_B02 / analysis sum "Assessed" center format=check.;

define nFWA_B03 / analysis sum "ID'd Areas for Improvement" center format=check.;

define nFWT_N11 / analysis sum "Cafeterias" center format=check.;

define nFWT_N12 / analysis sum "Vending" center format=check.;

define nFWT_N13 / analysis sum "Snack Bars" center format=check.;

define nFWT_N14 / analysis sum "Behavioral Design" center format=check.;

define nFWT_P11 / analysis sum "Policy" center format=check.;

define nFWT_P12 / analysis sum "Environmental Supports" center format=check.;

define nFWT_P13 / analysis sum "Walking Program" center format=check.;

define nFWT_P14 / analysis sum "Activity Friendly Routes" center format=check.;

rbreak after /summarize dol ;

compute after ;

FW_idname='Total';

call define (_row_, "style", "STYLE=[font_weight=bold]") ;

call define (2,"format","3.");

call define (3,"format","3.");

call define (4,"format","3.");

call define (5,"format","3.");

call define (6,"format","3.");

call define (7,"format","3.");

call define (8,"format","3.");

call define (9,"format","3.");

call define (10,"format","3.");

call define (11,"format","3.");

call define (12,"format","3.");

endcomp;

run;

** 1b.OUTCOMES;

proc report data=convert;

where grantee="&G" and FW_idname ne '';

column ("Site Level Outcomes" FW_IDname ("Nutrition Outcomes" nFWO_N05 ("FSGs Implemented in" nFWO_N02-nFWO_N04) ) ("Physical Activity Outcomes" nFWO_P01 ("Practice Implemented" nFWO_P02-nFWO_P04)));

define FW_idname / group display "Worksite ID|Name" center;

define nFWO_N02 / analysis sum "Cafeterias" center format=check.;

define nFWO_N03 / analysis sum "Vending Machines" center format=check.;

define nFWO_N04 / analysis sum "Snack Bars" center format=check.;

define nFWO_N05 / analysis sum "Behavioral Design Practice Implemented" center format=check.;

define nFWO_P01 / analysis sum "Policy Adopted/Improved" center format=check.;

define nFWO_P02/ analysis sum "Environmental Support" center format=check.;

define nFWO_P03 / analysis sum "Walking Program" center format=check.;

define nFWO_P04 / analysis sum "Activity Friendly Route" center format=check.;

rbreak after/summarize dol ;

compute after;

FW_idname='Total';

call define (_row_, "style", "STYLE=[font_weight=bold]");

call define (2,"format","3.");

call define (3,"format","3.");

call define (4,"format","3.");

call define (5,"format","3.");

call define (6,"format","3.");

call define (7,"format","3.");

call define (8,"format","3.");

call define (9,"format","3.");

endcomp;

run;

**1c.NARRATIVE;

proc report data=convert;

column FW_nar;

define FW_nar / display "&Q Narrative";

where FW_Nar ne "" and grantee="&G";

*where quarter= "&Q";

run;

*ods rtf close;

%mend granteereport;

%granteereport (??)

Re: help w macro in combo w proc report

Reeza — Fri, 22 Oct 2021 16:17:57 GMT

Look up CALL EXECUTE.

See Step 5/6 here:

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Re: help w macro in combo w proc report

AMSAS — Mon, 25 Oct 2021 13:40:10 GMT

At a high level you are going to need to know about

Referencing Macro Variables Indirectly

%do statement

Then you are going to do something like this:

/* create macro to run reports */
/* runs parameter passes the number of runs */
%macro multiRep(runs) ;
	/* do loop to loop through the runs */
	%do i=1 %to &runs ;
		/* msg for the log */
		%put Run Report &i ;
		/* simple PROC PRINT example */
		proc print data=sashelp.class (obs=&i) ;
			title "Run Report &i" ;
		run ;
	%end ;
%mend ;

/* Run macro with 5 runs */
%multiRep(5) ;

Start simple and build up the macro little by little.

topic Re: help w macro in combo w proc report in New SAS User

help w macro in combo w proc report

Re: help w macro in combo w proc report

Re: help w macro in combo w proc report