topic Select last value in a grouping in SAS Programming

Select last value in a grouping

podarum — Sat, 04 Nov 2017 20:20:52 GMT

Hi, I an trying to select the last value/s of goupings in a dataset, here's what I mean:

HAVE

Acct1 Index ValA

001 1 234

001 1 245

001 1 897

001 2 387

001 2 567

001 5 456

001 5 478

002 1 5

002 2 12

002 2 56

002 8 387

002 8 567

NEED

Acct1 Index ValA

001 5 456

001 5 478

002 8 387

002 8 567

Re: Select last value in a grouping

PeterClemmensen — Sat, 04 Nov 2017 21:19:45 GMT

One way..

data HAVE;
input Acct1$  Index    ValA;
datalines;
001        1         234
001        1         245
001        1         897
001        2         387
001        2         567
001        5         456
001        5         478
002        1         5
002        2         12
002        2         56
002        8         387
002        8         567
;

proc sql;
   create table want as
   select * from have 
   group by Acct1
   having Index=max(Index);
quit;

Re: Select last value in a grouping

Astounding — Sat, 04 Nov 2017 21:10:06 GMT

I'm sure SQL can handle this too, but I'm more comfortable with a DATA step:

data want;

do until (last.acct1);

set have;

by acct1;

end;

max_index = index;

do until (last.acct1);

set have;

by acct1;

if index = max_index then output;

end;

drop max_index;

run;

Re: Select last value in a grouping

Ksharp — Sun, 05 Nov 2017 10:07:33 GMT

Astounding,

Alternate DOW skill :

data HAVE;
input Acct1$  Index    ValA;
datalines;
001        1         234
001        1         245
001        1         897
001        2         387
001        2         567
001        5         456
001        5         478
002        1         5
002        2         12
002        2         56
002        8         387
002        8         567
;
data want;
do until (last.index);
   set have;
   by acct1 index;
end;
last_acct1=last.acct1;
do until (last.index);
   set have;
   by acct1 index;
   if last_acct1 then output;
end;
drop last_acct1;
run;