Proc report compute block for all columns

[ZacLopresti]

Hi all,

I have a report that runs with the potential of having a different amount of columns depending on the raw data.

I'm using a proc report compute block to try and highlight specific cells. Through testing, i have got the code to behave the way I want whilst setting the column name manually as a macro variable but I'd like the compute block to apply all columns regardless of the number of columns or their names etc.

What's the best way to do this?

Below is the reporting step with the macro variable &COLUMN set to an individual column name.

PROC REPORT 
DATA=OUTPUT_%sysfunc(trim(&BATTERY))_%sysfunc(trim(&REPORT_TXT))  NOWD NOCENTER
	style(column)=[width=12mm]
	STYLE(header)=[background=skyblue];

	where date <> '.'d;

    COLUMNS ("Coke Ovens Emission Survey Results Table.    Battery:  &Battery   Location:  &REPORT_TXT" &sorted_cols);
	DEFINE DATE / order style=[cellwidth=20mm];

	COMPUTE &COLUMN;
		if SUBSTR(&COLUMN,3,1) = '' and &COLUMN ^= '' then do; /*Bookable emissions only*/
     		call define(_col_, "style", "style=[backgroundcolor=orange]");
		end;
	endcomp;
Run;

Thanks,

Zac

[andreas_lds]

Can you post an excerpt of the data and explain why you have a different number of variables? It seems as the data should be transposed, so that "across" could be used.

[ZacLopresti]

Thanks for the response!

I've added a sample and a screenshot below.

The change in the number of variables is a result of there not being data present for every single oven within the reporting period. As a result, ovens without text in the cell for any of the reporting period dates don't need to be displayed.

What do you mean by across?

data work.SAMPLE;
	infile datalines dlm=',';
	input DATE date9. OVEN_0051 $ OVEN_0052 $ OVEN_0054 $;
datalines;
12OCT2021, , ,TT(F)
13OCT2021,CD(N), ,BL
14OCT2021, ,TT, 
;
run; 

Thanks,

Zac

[andreas_lds]

I would start by transposing the data, so that you have one variable "oven" containing the text on the right side of the underscore and another variable for the value. Yes, this will make you dataset less human-readable, but maybe easier to process: you could use a where-statement to select all values that are "bookable emissions". If you have to list the ovens horizontally, the table will still have many, many empty cells.

I still have trouble figuring out how the report should look like, so this suggestion may lead to more problems.

[Kurt_Bremser]

After making your sample code work, here's an example of using a long dataset in PROC REPORT with ACROSS:

data work.SAMPLE;
infile datalines dlm=',' dsd truncover;
input DATE :date9. OVEN_0051 $ OVEN_0052 $ OVEN_0054 $;
format date yymmdd10.;
datalines;
12OCT2021, , ,TT(F)
13OCT2021,CD(N), ,BL
14OCT2021, ,TT, 
;

proc transpose
  data=sample
  out=long1 (
    rename=(col1=emission)
    where=(emission ne "")
  )
;
by date;
var oven:;
run;

data long2;
set long1;
length oven $4;
oven = scan(_name_,2,"_");
drop _name_;
run;

proc report data=long2;
column date emission,oven n;
define date / "Oven" group;
define emission / "" display;
define oven / "" across;
define n / noprint;
run;

And from the same dataset, it is now very easy to create a pivoted report:

proc report data=long2;
column oven emission,date n;
define oven / "Date" group;
define emission / "" display;
define date / "" across;
define n / noprint;
run;

Similar is true for all kinds of analysis. Always keep data out of structure.

Maxim 19: Long Beats Wide.

[ZacLopresti]

Thanks for the reply.

I think I understand all that you're saying and the code makes sense.

In this approach at which stage would you try to highlight the cells?

[ballardw]

Some data

Code without macro variables using that data

Stuff like

if SUBSTR(&COLUMN,3,1) = '' and &COLUMN ^= ''

might be an indication that the data could use some modification prior to the report or possibly even a format could be used instead of logic to assign highlight colors

[ZacLopresti]

Ill look into that thanks.
Do you have any good examples?