topic Calculating a relative value using proc report in ODS and Base Reporting

Calculating a relative value using proc report

deleted_user — Wed, 04 Jun 2008 08:20:29 GMT

Hi

I have a situation whereby i need to calculate a value for each row, relative to the first row.

ie. if i have the following table

a b c
d e f
g h i

then i wish to calculate

a b c/c
d e f/c
g h i/c

I have been able to do this using data steps and tabulates however because i repeat the calculation many times it is very time consuming and computationally demanding.

I have also been able to calculate the value for consecutive rows, but cannot keep dividing by the value c.

Any help would be greatly appreciated.
Chis

Re: Calculating a relative value using proc report

Tim_SAS — Wed, 04 Jun 2008 12:35:00 GMT

Does this work for you? It assumes that z will never be missing.

[pre]
data;
input x y z;
datalines;
1 2 3
4 5 6
7 8 9
;;;;
proc report nowd;
col x y z z2;
define x--y / display;
define z / display noprint;
define z2 / computed;
compute z2;
if keep = . then keep = z;
z2 = z / keep;
endcomp;
run;[/pre]

Produces
[pre]
x y z2
1 2 1
4 5 2
7 8 3
[/pre]

Re: Calculating a relative value using proc report

deleted_user — Wed, 04 Jun 2008 15:57:51 GMT

That's a fantastic solution, excellent bit of code.

Thanks,
Chris

Re: Calculating a relative value using proc report

deleted_user — Fri, 06 Jun 2008 12:47:41 GMT

Hi again,

I've used the bit of code supplied ealier and it has worked well, however i have been unable to adapt it to deal with the following problem:

If i have a report that is grouped by month, i need the relative figure to be figured out independantly for each month.

ie. if i have

month x y z

Jan 1 2 3
Jan 4 5 6
Jan 7 8 9
Feb a d c
Feb d e f
Feb h i j

I need to calculate

Jan 1 2 3 3/3
Jan 4 5 6 6/3
Jan 7 8 9 9/3
Feb a b c c/c
Feb d e f f/c
Feb g h i i/c

Any help would be greatly appreciated.

Thanks,
Chris