Hello, I have a dataset showing number of labs received each week for varying number of weeks, where the week numbers will change depending on when the report is run and how far into the past the user chose to look. I want to highlight when volume is unusually high or low for each week. The following program does not work, but is along the lines of what I was trying to do.
proc report data=data nowd; column labname testname flag2 mean std week:; define LabName / display "Lab Name"; define TestName / display "Test Name"; define flag2 / display ; define mean/ display ; define std / display ; define week: / display; compute week:; if week: > (mean + (2*std)) then call define(_col_,"style","style={background=yellow}") /*highlight significant drops in yellow*/; else if week: < (mean - (2*std)) then call define(_col_,"style","style={background=green}") /*highlight significant increases in green*/; endcomp; run;
I get the following error:
proc report data=data nowd;
2158 column labname testname flag2 mean std week:;
2159 define LabName / display "Lab Name";
2160 define TestName / display "Test Name";
2161 define flag2 / display ;
2162 define mean/ display ;
2163 define std / display ;
2164 define week: / display;
2165
2166 compute week:;
-
22
200
ERROR 22-322: Syntax error, expecting one of the following: ;, /.
ERROR 200-322: The symbol is not recognized and will be ignored.
2167
2168 if week: > (mean + (2*std)) then call define(_col_,"style","style={background=yellow}")
2168! /*highlight significant drops in yellow*/;
2169 else if week: < (mean - (2*std)) then call
2169! define(_col_,"style","style={background=green}") /*highlight significant increases in green*/;
2170
2171 endcomp;
2172
2173 run;
Any ideas on how to get this to work? I've considered using a proc format with proc print, but can't figure out how to get the conditional formatting to work.
You can't use the colon in the compute statement in the compute statement
compute week:;
It seems as if you have many week variables, perhaps named something like week22 week23 ... This is a very wide layout of the data, and this is not a good layout of the actual data set because of the issues you are now running into. Better to have a variable named week with values 22, 23, ... which is a long layout of the data; and use Week as an across variable in PROC REPORT. There are many examples at sas.com and elsewhere of using ACROSS variables.
I also (again) steal Maxim 19 from @Kurt_Bremser 's maxims:
Maxim 19
Long beats wide.
(Don't keep data in structure)
In the world of spreadsheets, people tend to line up data side-by-side, and put data items (dates, categories, …) into column headers. This runs counter to all the methods available in SAS for group processing, and makes programming difficult, as one has variable column names and has to resort to creating dynamic code (with macros and/or call execute) where such is not necessary at all if categories were represented in their own column and data aligned vertically.
There are times where a wide format is needed, eg when preparing data for regression analysis. But for the processing and storing of data, long formats are always to be preferred.Dynamic variable names force unnecessary dynamic code.
You can't use the colon in the compute statement in the compute statement
compute week:;
It seems as if you have many week variables, perhaps named something like week22 week23 ... This is a very wide layout of the data, and this is not a good layout of the actual data set because of the issues you are now running into. Better to have a variable named week with values 22, 23, ... which is a long layout of the data; and use Week as an across variable in PROC REPORT. There are many examples at sas.com and elsewhere of using ACROSS variables.
I also (again) steal Maxim 19 from @Kurt_Bremser 's maxims:
Maxim 19
Long beats wide.
(Don't keep data in structure)
In the world of spreadsheets, people tend to line up data side-by-side, and put data items (dates, categories, …) into column headers. This runs counter to all the methods available in SAS for group processing, and makes programming difficult, as one has variable column names and has to resort to creating dynamic code (with macros and/or call execute) where such is not necessary at all if categories were represented in their own column and data aligned vertically.
There are times where a wide format is needed, eg when preparing data for regression analysis. But for the processing and storing of data, long formats are always to be preferred.Dynamic variable names force unnecessary dynamic code.
You could try ARRAY in PROC REPORT.
proc report data=sashelp.class nowd;
columns name sex age weight height dummy;
define name / display "Lab Name";
define sex / display "Test Name";
define _numeric_ / display;
define dummy/computed noprint;
compute dummy;
array x{*} age weight height;
do i=1 to dim(x);
if x{i}<14 then call define(vname(x{i}),"style","style={background=yellow}") /*highlight significant drops in yellow*/;
else if x{i}>65 then call define(vname(x{i}),"style","style={background=green}") /*highlight significant increases in green*/;
end;
endcomp;
run;
This also worked for me, thank you so much!
Instead of transposing to wide before PROC REPORT, keep the long layout and use the week number as an ACROSS variable. And you probably can calculate flag2, mean and std in the REPORT procedure as well.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.