BookmarkSubscribeRSS Feed
rohit_prajapati
Calcite | Level 5

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;

11 REPLIES 11
Ksharp
Super User

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

EdvinasS
Calcite | Level 5

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

rohit_prajapati
Calcite | Level 5

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

shivas
Pyrite | Level 9

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

Linlin
Lapis Lazuli | Level 10

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

Pallav
Calcite | Level 5

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;

Linlin
Lapis Lazuli | Level 10

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

MikeZdeb
Rhodochrosite | Level 12

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;

MikeZdeb
Rhodochrosite | Level 12

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

Haikuo
Onyx | Level 15

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.

rohit_prajapati
Calcite | Level 5

Thank you all  Smiley Happy

Pallav code is long but really Easy Smiley Happy

Regards,

Rohit

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1446 views
  • 2 likes
  • 8 in conversation