topic Re: vertical sum in SAS Procedures

vertical sum

deleted_user — Tue, 02 Jun 2009 14:36:57 GMT

Hi guys. I'm quite new to SAS, hope you can help.

I have this data set:

data i;
input month monthly_sum month1 month2 month3 month4 month5;
cards;
1 10 1 1 1 1 1
2 20 2 2 2 2 .
3 30 3 3 3 . .
4 40 4 4 . . .
5 50 5 . . . .
6 60 . . . . .
;

What I want to do is create a new variable that calculates the vertical sum of all month1-month5 variables individually and then divides that sum with total vertical sum of variable monthly_sum. But - the number of obs included in the calculation (vertically) is 1 less for each column, and it starts off 1 less each time (in this example 5 and not 6 obs)

Like this:
Ex. sum of month1=15
sum of monthly_sum=150.
new variable (15/100) =0,1

Ex. sum of month2=10
sum of monthly_sum=100.
new variable (10/100)=0,1

Ex. sum of month3=6
sum of monthly_sum=60.
new variable (6/60) =0,1

Problem here is there will be different no of obs all the time, varying from 4 up to 2000.

Re: vertical sum

deleted_user — Tue, 02 Jun 2009 16:55:42 GMT

One way of acheive this by using arrays. NEW{I} is the variable you want at the end...run it let me know if it doesn't work.

data j;
set i nobs=n;
array m{5} month1-month5;
array s{5} (0,0,0,0,0);
array sum1{5} (0,0,0,0,0);
array new{5};
do i=1 to 5;
s{i}=sum(s{i},m{i});
if _n_ le n-i then sum1{i}=sum(sum1{i},monthly_sum);
new{i}=s{i}/sum1{i};
end;
put _all_;
run;

~ Sukanya E

Re: vertical sum

deleted_user — Wed, 03 Jun 2009 08:06:54 GMT

Hi Sukanya.

It works like a charm, thank you.

Since the number of "months" and "monthly_sum" varies between say 4-500 every time I run the code, do you have any idea on how to prepare the arrays to handle the different number of obs ?

Something like:
array m{*} month1-month*
array s{*} (dim)

do i=1 to dim(m)

Re: vertical sum

deleted_user — Wed, 03 Jun 2009 10:43:44 GMT

No worries, Sukanya.
I solved it with a call,

(original code)
data call;
set max;
call symput ('n', put(no, 2.));
run;

And the array:
data w_avg;
set prepare nobs=n;
format new1-new&n NUMX12.4;
array m{&n} monthsonbooks1-monthsonbooks&n;
array s{&n} (0-&n);
array sum1{&n} (0-&n);
array new{&n};
do i=1 to &n;
s{i}=sum(s{i},m{i});
if _n_ le n-i then sum1{i}=sum(sum1{i},ORIGLMT_Sum);
new{i}=s{i}/sum1{i};
end;
put _all_;
run;