The SAS Output Delivery System and reporting techniques

Calculating a relative value using proc report

Reply
N/A
Posts: 0

Calculating a relative value using proc report

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
Super Contributor
Posts: 394

Re: Calculating a relative value using proc report

Posted in reply to deleted_user
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]
N/A
Posts: 0

Re: Calculating a relative value using proc report

Posted in reply to deleted_user
That's a fantastic solution, excellent bit of code.

Thanks,
Chris
N/A
Posts: 0

Re: Calculating a relative value using proc report

Posted in reply to deleted_user
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
Ask a Question
Discussion stats
  • 3 replies
  • 121 views
  • 0 likes
  • 2 in conversation