proc report - problem with computed col

[Konkordanz]

Hi,

within the proc report command I want to create a new column sp3 as a sum of three other columns (sp4, sp7, sp10). I tried it with following syntax:

proc report data=schu2;
column bundesland bev sp3 sp4 sp5 sp6 sp7 sp8 sp9 sp10 sp11 sp12 sp13 sp14 sp15;
define bundesland /display;
define bev /display format=Format.;
define sp4  /display format=Format.;
define sp5  /display format=Format.;
define sp6  /display format=Format.;
define sp7  /display format=Format.;
define sp8  /display format=Format.;
define sp9  /display format=Format.;
define sp10 /display format=Format.;
define sp11 /display format=Format.;
define sp12 /display format=Format.;
define sp13 /display format=Format.;
define sp3 / computed format=Format.;
compute sp3; 
	sp3 =sp4+sp7+sp10;
endcomp;
run;

Problem: That doesnt work. Alternativ I tried _c4_+_c7_+_c10_. That doesnt work neither. Afterwards I wanted to know, if the syntax in general is correct...so I tried sp3=1+2. That works. So...Do you know where the error lies?

Thank you!

[FreelanceReinh]

Hi @Konkordanz,

The problem is that the report items are processed from left to right, but your COMPUTE block for sp3 refers to variables that appear on the right side of sp3 in the COLUMN statement, so their values are not yet available when sp3 is computed. The easiest fix would be to move sp3 to the end of the COLUMN statement (or at least to the right of sp10). Of course, this would change the column order.

One way to avoid this change is to introduce aliases for the variables used in the COMPUTE block. You can then use these "copies" of sp4, sp7 and sp10 and hide them with NOPRINT:

proc report data=schu2;
column bundesland bev sp4=sp_4 sp7=sp_7 sp10=sp_10 sp3 sp4-sp15;
define bundesland / display;
define bev        / display format=Format.;
define sp_4       / noprint;
define sp_7       / noprint;
define sp_10      / noprint;
define sp4-sp13   / display format=Format.;
define sp3        / computed format=Format.;
compute sp3; 
  sp3 = sp_4+sp_7+sp_10;
endcomp;
run;

[PaigeMiller · Accepted solution]

The way PROC REPORT works is left to right. So when sp3 comes to the left of sp4 and left of sp7 and left of sp10, the math can't be done because as sp3 is being computed, it does not know about the values of sp4 and sp7 and sp10.

[Konkordanz]

Okay, thank you!

[Kurt_Bremser]

Since sp3 comes before the other variables in the COLUMN statement, and variables are resolved by the order defined in that statement, you get missing values.

Define "hidden" aliases which you can use in the COMPUTE:

proc report data=schu2;
column bundesland bev sp4=s4 sp7=s7 sp10=s10 sp3 sp4 sp5 sp6 sp7 sp8 sp9 sp10 sp11 sp12 sp13 sp14 sp15;
define bundesland /display;
define s4 / noprint;
define s7 / noprint;
define s10 / noprint;
define bev /display format=Format.;
define sp4  /display format=Format.;
define sp5  /display format=Format.;
define sp6  /display format=Format.;
define sp7  /display format=Format.;
define sp8  /display format=Format.;
define sp9  /display format=Format.;
define sp10 /display format=Format.;
define sp11 /display format=Format.;
define sp12 /display format=Format.;
define sp13 /display format=Format.;
define sp3 / computed format=Format.;
compute sp3; 
  sp3 = sum(s4,s7,s10);
endcomp;
run;

The SUM() function takes care of missing values in the variables (counting them  as 0). You will now get a missing value only when all three source variables are missing.