How to obtain the most recent observation in a group?

[adhunamukherjee]

Hello SAS Communities, 

I have the following dataset HypTabs.Vitals:

Obs    SSN                 VisitDt         Ht       Wt    SBP  DBP

1

000-00-0001

13JAN2010

66

149

117

79

2

000-00-0001

21JAN2011

66

149

117

80

3

000-00-0001

13JAN2012

66

148

121

80

4

000-00-0001

22JAN2013

66

153

111

79

5

000-00-0001

15JAN2014

66

152

116

79

6

000-00-0002

14JUN2010

63

141

118

72

7

000-00-0002

20JUN2012

63

140

118

76

8

000-00-0002

17JUN2013

63

138

117

74

9

000-00-0002

17JUN2014

63

138

111

72

10

000-00-0003

03JAN2011

66

149

120

77

11

000-00-0003

02JAN2012

66

151

111

76

12

000-00-0003

31DEC2012

66

152

126

78

I want to create a dataset with only the last observation per SSN group. So, my final dataset will be as follows:

Obs    SSN                 VisitDt         Ht       Wt    SBP  DBP

1

000-00-0001

15JAN2014

66

152

116

79

2

000-00-0002

17JUN2014

63

138

111

72

3

000-00-0003

31DEC2012

66

152

126

78

Can you help me with the SAS DATA Step which will help me create the above temporary reduced dataset? 

Thanks,
Adhuna Mukhopadhyay

[Kurt_Bremser · Accepted solution]

SQL:

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

Data step:

proc sort data=have;
by ssn visitdt;
run;

data want;
set have;
by ssn;
if last.ssn;
run;

[mdakkak]

Hi,

The way I was able to do it was to include IF LAST.SSN in my code. This allows you to only obtain the most recent visit from a single SSN. 

DATA WORK.VitalLast;
	SET HYPTABS.Vitals;
	BY SSN;
	IF LAST.SSN;
RUN;

Hope that helps!

[bfreeman0402]

Hi!

I'm not sure if by most recent observation you meant most recent date (your questions states most recent observation in a group however the temporary data set does not reflect this), BUT since your question appears to have already been answered I will answer as if you wanted to obtain the most recent observation (by date) using an alternative to the answers already provided. I want to say you may be able to sort SSN by ascending and LastVisit by descending and use NODUPKEY to only keep the first observation. 

Best,

Brandon