How to use proc report with same compute block for many variables beginning with the same name

[sasuser80s]

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.

[PaigeMiller · Accepted solution]

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.

[Ksharp]

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;

[sasuser80s]

This also worked for me, thank you so much!

[Kurt_Bremser]

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.