topic Re: Get last observation from group in SAS Procedures

Get last observation from group

Loko — Wed, 22 Apr 2015 12:51:51 GMT

Dear all,

For the following sample dataset I need to retrieve the last value of c within each group (variable a) from the latest b.

data have;

input a $1-4 b c;

datalines;

M31 1 2

M31 3 9

M31 2 15

M98 5 4

M98 3 6

M98 4 2

;

run;

Therefore the output shall be M31 3 9 and M98 5 4.

I know the classic way of sort and last but I am curious if there is any other method whivh may run faster (my dataset is big...)

10x

Re: Get last observation from group

stat_sas — Wed, 22 Apr 2015 13:14:47 GMT

proc sql;

select * from have

group by a

having b=max(b);

quit;

Re: Get last observation from group

Steelers_In_DC — Wed, 22 Apr 2015 13:31:56 GMT

That's the only other way I would think to do it. I'm curious to hear how the performance changed from sort / last.

Re: Get last observation from group

Jagadishkatam — Wed, 22 Apr 2015 13:42:15 GMT

I agree with you Mark, sort followed by last. could also be a good choice.

data have;

input a $1-4 b c;

datalines;

M31 1 2

M31 3 9

M31 2 15

M98 5 4

M98 3 6

M98 4 2

;

run;

proc sort data=have;

by a b;

run;

data want;

set have;

by a b;

if last.a;

run;

Thanks,

Jag

Re: Get last observation from group

Reeza — Wed, 22 Apr 2015 13:48:29 GMT

I think there's a proc summary solution here but I've never been good at optimization problems.

Re: Get last observation from group

RW9 — Wed, 22 Apr 2015 13:55:29 GMT

Well, you don't need to sort the data first to get last. Proc SQL obviously is quite good, however you may run into memory limits depending on the size of your data. I would suggest adding in a last flag e.g:

data your_data;

set your_data;

by a;

retain max_num max_num_res;

if first.a then do;

max_num=b;

max_res=c;

end;

else do;

if b > max_num then do;

max_num=b;

max_num_res=c;

end;

if last.a then output;

run;

Then you should have one row per group, with max_num and max_num_res holding the data you want. You will have to test performance of course against SQL and Hash.

Re: Get last observation from group

Haikuo — Wed, 22 Apr 2015 14:14:52 GMT

Some level of SORT will always be involved in this case, including SQL,Hash (implicit) or even the one offered by (explicit) Here is one example using Hash:

data have;

input a $1-4 b c;

datalines;

M31 1 2

M31 3 9

M31 2 15

M98 5 4

M98 3 6

M98 4 2

;

run;

data want;

declare hash h(ordered:'a');

h.definekey('b');

h.definedata('b','c');

h.definedone();

declare hiter hi('h');

do until (last.a);

set have;

by a notsorted;

rc=h.add();

end;

rc=hi.last();

output;

drop rc;

run;

Re: Get last observation from group

Reeza — Wed, 22 Apr 2015 14:16:50 GMT

proc summary data=have noprint;

class a;

id c;

var b;

output out=want max=;

run;

proc print data=want;

run;