Producing the same report for each instance of a variable

[plzsiga]

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;

[Reeza]

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&docset...