I'm using this code below, but I'd like to add 2 more by-groups.. ? Thanks
data by
patient;
retain num_sum 0
if first.patient then do;
count=0;
num sum=0;
end;
copunt+1;
last&n=lag&n(num);
if count gt &n then num_sum=sum(num_sum,num,-last&n);
else num_sum=sum(num_sum,num);
if count ge &n then mov_aver=num_sum/&n;
else mov_aver=.;
run;
Do you have ETS licensed?
If so check out proc expand and the transformation example.
Yes Reeza
Thanks, that's actually a good approach too. but wonder if I can use a by statement
Depends on what you're looking for as always.
You can set multiple values within a by statement, but they end up 'nested' rather than multiple level. Perhaps some sample data and expected results.
here is a sample code that I'm trying.. My goal is to get a 6 month moving average by both the FSA and Type. But all I'm getting is by FSA, and it ignores where the Property starts.
%let n = 6;
data out_data;
set in_data;
by FSA Type;
retain price_sum 0;
if first.FSA and first.Type then do;
count=0;
price_sum=0;
end;
count+1;
last&n=lag&n(price);
if count gt &n then price_sum=sum(price_sum,price,-last&n);
else price_sum=sum(price_sum,price);
if count ge &n then mov_aver=price_sum/&n;
else mov_aver=.;
run;
Sample Data, to do a 2 month rolling average for simplicity:
FSA Type Price Date
B1A A 2 2001.01
B1A A 3 2001.02
B1A A 4 2001.03
B1A B 1 2001.01
B1A B 2 2001.02
B1A B 3 2001.03
B2M A 6 2002.03
B2M A 7 2002.04
B2M B 1 2002.03
B2M B 2 2002.04
Expect to get:
FSA Type Price Date Mov_Avg
B1A A 2 2001.01 -
B1A A 3 2001.02 2.5
B1A A 4 2001.03 3.5
B1A B 1 2001.01 -
B1A B 2 2001.02 1.5
B1A B 3 2001.03 2.5
B2M A 6 2002.03 -
B2M A 7 2002.04 6.5
B2M B 1 2002.03 -
B2M B 2 2002.04 1.5
Then Stack Skill is very helpful. It is from Arthur.Carpenter .
data x; input FSA $ Type $ Price Date $ ; datalines; B1A A 2 2001.01 B1A A 3 2001.02 B1A A 4 2001.03 B1A B 1 2001.01 B1A B 2 2001.02 B1A B 3 2001.03 B2M A 6 2002.03 B2M A 7 2002.04 B2M B 1 2002.03 B2M B 2 2002.04 ; run; data want; set x; array p{2} _temporary_; if type ne lag(type) then call missing(of p{*}); p{mod(_n_,2)+1}=price; mov_avg=mean(of p{*}); if type ne lag(type) then call missing(mov_avg); run;
Ksharp
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.