BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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.
3 REPLIES 3
deleted_user
Not applicable
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
deleted_user
Not applicable
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)
deleted_user
Not applicable
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 3559 views
  • 0 likes
  • 1 in conversation