Help using Base SAS procedures

Keep last two observations

Reply
Contributor
Posts: 73

Keep last two observations

Hi

are there any easy way to keep the last

two observations within a group?

Thanks

thomas

Super User
Posts: 19,855

Re: Keep last two observations

Posted in reply to bollibompa

Do you have another variable that controls the sort as well as your group variable?

Super User
Posts: 5,516

Re: Keep last two observations

Posted in reply to bollibompa

Easy?  No.  But at least it's not terribly lengthy:

data want;

   total_count=0;

   do until (last.group);

      set have;

      by group;

      total_count + 1;

   end;

   running_count=0;

   do until (last.group);

      set have;

      by group;

      running_count + 1;

      if running_count >= total_count - 1 then output;

   end;

   drop running_count total_count;

run;

Good luck.

Trusted Advisor
Posts: 1,228

Re: Keep last two observations

Posted in reply to bollibompa

data have;
input group a $ b c;
datalines;
1 A 23 20
1 B 34 32
1 C 29 12
1 D 24 10
2 E 45 65
2 F 87 25
2 G 89 76
;

data want;
set have;
by group;
if first.group then cnt=1;
else cnt+1;
run;

proc sql;
select * from want
group by group
having cnt>max(cnt)-2
order by group,cnt;
quit;

Respected Advisor
Posts: 3,799

Re: Keep last two observations

Posted in reply to bollibompa

Consider this PROC SUMMARY.

proc summary data=sashelp.class nway;
  
class age;
   output out=last2 idgroup(last obs out[2](_all_)=);
   run;

8-13-2014 10-57-19 AM.png

Or using _OBS_: from this step consider this.

data classL2;
   set last2(keep=age _obsSmiley Happy;
   array x
  • _obs:;
  •    do i = dim(x) to 1 by -1;
         
    if missing(x) then continue;
          point = x;
          obs = x;
         
    set sashelp.class point=point;
          output;
         
    end;
      
    drop _obs: i;
       run;

    8-13-2014 11-01-00 AM.png
    Super User
    Posts: 10,041

    Re: Keep last two observations

    Posted in reply to bollibompa

    How about :

    data have;
    input group a $ b c;
    datalines;
    1 A 23 20
    1 B 34 32
    1 C 29 12
    1 D 24 10
    2 E 45 65
    2 F 87 25
    2 G 89 76
    ;
    run;
    data want(drop=_group);
     merge have have(keep=group rename=(group=_group) firstobs=3);
     if group ne _group;
    run;
    

    Xia Keshan

    Respected Advisor
    Posts: 3,156

    Re: Keep last two observations

    Nice! Keep it coming, buddy!

    N/A
    Posts: 1

    Re: Keep last two observations

    Posted in reply to bollibompa

    Here's another option

    data have;

    input group a $ b c;

    datalines;

    1 A 23 20

    1 B 34 32

    1 C 29 12

    1 D 24 10

    2 E 45 65

    2 F 87 25

    2 G 89 76

    ;

    run;

    proc sort data=have;

      by  descending group descending a ;

    run;

    data want;

      set have;

      by descending group descending a;

      retain icount;

      if first.group then icount=0;

      if icount<2 then do;

      output;

      icount++1;

      end;

      drop icount;

    run;

    proc sort data=want;

      by group a;

    run;

    Contributor
    Posts: 73

    Re: Keep last two observations

    Hi,

    Problem solved!

    Many thanks for all brilliant answers!

    /Thomas

    Ask a Question
    Discussion stats
    • 8 replies
    • 1229 views
    • 5 likes
    • 8 in conversation