DATA Step, Macro, Functions and more

Select last value in a grouping

Accepted Solution Solved
Reply
Super Contributor
Posts: 409
Accepted Solution

Select last value in a grouping

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

 

 


Accepted Solutions
Solution
‎11-04-2017 06:30 PM
PROC Star
Posts: 1,215

Re: Select last value in a grouping

[ Edited ]

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;

View solution in original post


All Replies
Solution
‎11-04-2017 06:30 PM
PROC Star
Posts: 1,215

Re: Select last value in a grouping

[ Edited ]

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;
Super User
Posts: 6,632

Re: Select last value in a grouping

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;

Super User
Posts: 10,689

Re: Select last value in a grouping

Posted in reply to Astounding

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 154 views
  • 1 like
  • 4 in conversation