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
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;
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.