Producing the same report for each instance of a variable

plzsiga — Tue, 10 Oct 2017 15:42:24 GMT

I can use this code to produce a report in and excel file for 1 of the 49 schools in my district. I would like to be able to produce separate excel files for each of the 49 schools that maintains the proc report features and picks up the school name (SchoolName1...schoolname2....Schoolname 49) in the both filename and the where statement.

It is possible that some of the schools would have no data, and although I could send them an empty file, that would waste their time and mine.

Any suggestions, simplifications or alterations to the code would be appreciated.

ODS TAGSETS.EXCELXP
file='D:\ADAM\SAS Practice\sG 2018\byschool\ELASchoolName1.xls'
STYLE=minimal
OPTIONS ( Orientation = 'landscape'
FitToPage = 'yes'
Pages_FitWidth = '1'
Pages_FitHeight = '100' );

Proc Report data=targela nowd ;
where school eq 'SchoolName1';

column Student StudentId Grade School ELA2017 ELALevel2016 ELALeveL2017 EAL17 ProfDrop BQELA ELGtarget ELAptsNeeded
;
Define Student/display;
Define StudentId/display;
Define Grade/display;
Define School/display;
Define ELA2017/display;
Define ELALevel2016/display;
Define ELALeveL2017/display;
Define EAL17/display;
Define ProfDrop/display;
Define BQELA/display;
Define ELGtarget/display;
Define ELAptsNeeded/display;

Compute ELALevel2016;;
if ELALevel2016 = 1 then call define(_col_,"style","style={background=red}") ;
else if ELALevel2016 = 2 then call define(_col_,"style","style={background=yellow}") ;
else if ELALevel2016 = 3 then call define(_col_,"style","style={background=green}") ;
else if ELALevel2016 = 4 then call define(_col_,"style","style={background=blue}") ;
else if ELALevel2016 = 5 then call define(_col_,"style","style={background=violet}") ;
endcomp;

Compute ELALevel2017;;
if ELALevel2017 = 1 then call define(_col_,"style","style={background=red}") ;
else if ELALevel2017 = 2 then call define(_col_,"style","style={background=yellow}") ;
else if ELALevel2017 = 3 then call define(_col_,"style","style={background=green}") ;
else if ELALevel2017 = 4 then call define(_col_,"style","style={background=blue}") ;
else if ELALevel2017 = 5 then call define(_col_,"style","style={background=violet}") ;
endcomp;

Compute EAL17;;
if EAL17 = 1.1 then call define(_col_,"style","style={background=red}") ;
else if EAL17 = 1.2 then call define(_col_,"style","style={background=coral}") ;
else if EAL17 = 1.3 then call define(_col_,"style","style={background=pink}") ;
else if EAL17 = 2.1 then call define(_col_,"style","style={background=orange}") ;
else if EAL17 = 2.2 then call define(_col_,"style","style={background=yellow}") ;
else if EAL17 = 3 then call define(_col_,"style","style={background=green}") ;
else if EAL17 = 4 then call define(_col_,"style","style={background=blue}") ;
else if EAL17 = 5 then call define(_col_,"style","style={background=violet}") ;

endcomp;

run;

quit;
ods tagsets.excelxp close;

Re: Producing the same report for each instance of a variable

Reeza — Tue, 10 Oct 2017 15:51:30 GMT

Use a macro.

See the examples here on how to create a macro:

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

Here's an example of how you can deal with an 'empty' data set:

http://documentation.sas.com/?docsetId=mcrolref&docsetTarget=p011imau3tm4jen1us2a45cyenz9.htm&docsetVersion=9.4&locale=en

topic Producing the same report for each instance of a variable in ODS and Base Reporting

Producing the same report for each instance of a variable

Re: Producing the same report for each instance of a variable