topic Re: Keep last two observations in SAS Procedures

Keep last two observations

bollibompa — Wed, 13 Aug 2014 15:06:09 GMT

are there any easy way to keep the last

two observations within a group?

Thanks

thomas

Re: Keep last two observations

Reeza — Wed, 13 Aug 2014 15:08:34 GMT

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

Re: Keep last two observations

Astounding — Wed, 13 Aug 2014 15:47:18 GMT

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.

Re: Keep last two observations

stat_sas — Wed, 13 Aug 2014 15:52:48 GMT

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;

Re: Keep last two observations

data_null__ — Wed, 13 Aug 2014 16:01:52 GMT

Consider this PROC SUMMARY.

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

Or using _OBS_: from this step consider this.

data classL2;
   set last2(keep=age _obs:);
   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;

Re: Keep last two observations

Ksharp — Thu, 14 Aug 2014 13:59:08 GMT

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

Re: Keep last two observations

Haikuo — Thu, 14 Aug 2014 14:25:21 GMT

Nice! Keep it coming, buddy!

Re: Keep last two observations

chrisz — Thu, 14 Aug 2014 23:49:51 GMT

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;

Re: Keep last two observations

bollibompa — Fri, 15 Aug 2014 07:24:26 GMT

Hi,

Problem solved!

Many thanks for all brilliant answers!

/Thomas