topic Re: Average visit of last three each subject in SAS Procedures

Average visit of last three each subject

rohit_prajapati — Tue, 11 Sep 2012 07:05:50 GMT

Hi,

I have below data.

OUTPUT - Average visit of last three each subjects.

DATA TEST;

INPUT SUBJECT $ VISIT;

DATALINES;

E101 10

E101 20

E101 30

E102 10

E102 20

E102 30

E103 10

E103 20

E103 40

E103 50

RUN;

Re: Average visit of last three each subject

Ksharp — Tue, 11 Sep 2012 07:27:39 GMT

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

Re: Average visit of last three each subject

EdvinasS — Tue, 11 Sep 2012 07:57:30 GMT

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

Re: Average visit of last three each subject

rohit_prajapati — Tue, 11 Sep 2012 08:42:23 GMT

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

Re: Average visit of last three each subject

shivas — Tue, 11 Sep 2012 09:19:43 GMT

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

Re: Average visit of last three each subject

Pallav — Tue, 11 Sep 2012 11:35:46 GMT

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 20

E101 30

E102 10

E102 20

E102 30

E103 10

E103 20

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;

Re: Average visit of last three each subject

Linlin — Tue, 11 Sep 2012 12:36:29 GMT

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

Re: Average visit of last three each subject

MikeZdeb — Tue, 11 Sep 2012 15:00:47 GMT

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;

Re: Average visit of last three each subject

Linlin — Tue, 11 Sep 2012 20:54:03 GMT

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

Re: Average visit of last three each subject

MikeZdeb — Tue, 11 Sep 2012 23:13:07 GMT

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

Re: Average visit of last three each subject

Haikuo — Wed, 12 Sep 2012 03:29:12 GMT

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;

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.

Re: Average visit of last three each subject

rohit_prajapati — Thu, 13 Sep 2012 03:59:18 GMT

Thank you all

Pallav code is long but really Easy

Regards,

Rohit