Help using Base SAS procedures

Average visit of last three each subject

Reply
Occasional Contributor
Posts: 17

Average visit of last three each subject

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;

Super User
Posts: 9,687

Re: Average visit of last three each subject

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

Contributor
Posts: 29

Re: Average visit of last three each subject

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

Occasional Contributor
Posts: 17

Re: Average visit of last three each subject

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

Super Contributor
Posts: 349

Re: Average visit of last three each subject

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

Super Contributor
Posts: 1,636

Re: Average visit of last three each subject

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

Occasional Contributor
Posts: 14

Re: Average visit of last three each subject

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;

Super Contributor
Posts: 1,636

Re: Average visit of last three each subject

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

Valued Guide
Posts: 765

Re: Average visit of last three each subject

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;

Valued Guide
Posts: 765

Re: Average visit of last three each subject

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 visitSmiley Happy;

run;


subject    mean

E101       90

E102       20

E103        3

Respected Advisor
Posts: 3,124

Re: Average visit of last three each subject

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 _Smiley Happy;

  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.

Occasional Contributor
Posts: 17

Re: Average visit of last three each subject

Thank you all  Smiley Happy

Pallav code is long but really Easy Smiley Happy

Regards,

Rohit

Ask a Question
Discussion stats
  • 11 replies
  • 416 views
  • 2 likes
  • 8 in conversation