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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.