Hi,
I have below data.
OUTPUT - Average visit of last three each subjects.
DATA TEST;
INPUT SUBJECT $ VISIT;
DATALINES;
E101 10
E101 10
E101 20
E101 30
E102 10
E102 20
E102 30
E103 10
E103 10
E103 20
E103 20
E103 40
E103 40
E103 50
RUN;
You don't post the result of output .
DATA TEST; INPUT SUBJECT $ VISIT; DATALINES; E101 10 E101 10 E101 20 E101 30 E102 10 E102 20 E102 30 E103 10 E103 10 E103 20 E103 20 E103 40 E103 40 E103 50 ; RUN; data want(drop=visit); set test; by subject; array x{0:2} _temporary_ ; x{mod(_n_,3)}=visit; if last.subject then do; mean=mean(of x{*}); output; call missing(of x{*});end; run;
Ksharp
Hi, i have similar issue and i believe you know the solutio. could you chech this please?
https://communities.sas.com/thread/37497
thanks a lot
Hi Ksharp,
I am not able to get desire output as i want.
i want output as "Average of all visits only for last three subject(group subject)".
Thanks
Rohit
Hi,
Can you post desire output.
I am guessing.....
proc summary data=test noprint;
class subject;
var visit;
output out=avg(drop=_type_) sum=Sum mean=Mean ;
run;
Thanks,
Shiva
Hi,
I believe Ksharp's code is what you have asked for. What do you want from the sample data in my post (I added the RED ones to your original data)?
DATA TEST;
INPUT SUBJECT $ VISIT;
DATALINES;
E101 10
E101 10
E101 20
E101 30
E101 80
E101 90
E101 100
E102 10
E102 20
E102 30
E103 10
E103 10
E103 20
E103 20
E103 40
E103 40
E103 50
E103 2
E103 3
E103 4
;
RUN;
data want(drop=visit);
set test;
by subject notsorted;
array x{0:2} _temporary_ ;
x{mod(_n_,3)}=visit;
if last.subject then do;
mean=mean(of x{*});
output;
call missing(of x{*});end;
run;
proc print;run;
obs SUBJECT mean
1 E101 90
2 E102 20
3 E103 3
If you want to have average of last three visit of each subject then following long but simple code might help.
DATA TEST;
INPUT SUBJECT $ VISIT;
DATALINES;
E101 10
E101 10
E101 20
E101 30
E102 10
E102 20
E102 30
E103 10
E103 10
E103 20
E103 20
E103 40
E103 40
E103 50
;
RUN;
proc sort data = test;
by subject;
Run;
data new look;
set test;
by subject;
if first.subject then flag=0;
flag +1;
output new;
if last.subject;
output look;
Run;
data final(drop = flag flag2);
merge look(rename= (flag = flag2))
new;
by subject;
if flag2-flag >2 then delete;
Run;
proc print data = final;
Run;
proc sql;
create table final1 as
select subject, avg(visit) as avg_visit
from final
group by subject;
quit;
proc print data= final1;
Run;
DATA TEST;
INPUT SUBJECT $ VISIT;
DATALINES;
E101 10
E101 10
E101 20
E101 30
E102 10
E102 20
E102 30
E103 10
E103 10
E103 20
E103 20
E103 40
E103 40
E103 50
;
data test1;
set test;
n=_n_;
proc sort;
by subject descending n;
run;
data test2;
set test1;
by subject notsorted;
count+(-first.subject*count)+1;
if count<4;
proc sql;
create table want as
select subject,mean(visit) as mean_visit
from test2
group by subject;
select * from want;
quit;
SUBJECT mean_visit
E101 20
E102 20
E103 43.33333
hi ...if there are always at least three subjects in a group ...
data means (keep=subject mean);
array x(10) _temporary_;
do j=1 by 1 until(last.subject);
set test;
by subject;
x(j) = visit;
end;
mean = mean(x(j),x(j-1),x(j-2));
call missing(of x(*));
run;
hi ... one more ...
proc summary data=test nway;
class subject;
output out=new idgroup(last out[3](visit)=);
run;
data new (keep=subject mean);
set new;
mean = mean(of visit:);
run;
subject mean
E101 90
E102 20
E103 3
Two more:
/* 2X DOW*/
DATA want;
do _n_=1 by 1 until (last.subject);
set test;
by subject;
end;
do _n=1 by 1 until (last.subject);
set test;
by subject;
if _n_-2<=_n<= _n_ then do;
_last3+visit;
_ct+1;
end;
end;
avg_3=_last3/_ct;
call missing(of _:);
keep subject avg_3;
run;
/*lag()*/
data want;
set test;
by subject;
if first.subject then call missing(ct);
ct+1;
avg_3=mean(ifn(ct<=1,.,lag1(visit)),ifn(ct<=2,.,lag2(visit)),visit);
if last.subject then output;
keep subject avg_3;
run;
Haikuo
Update: using mean() instead of hard coding.
Thank you all
Pallav code is long but really Easy
Regards,
Rohit
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.