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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.