topic How to obtain the most recent observation in a group? in SAS Programming

How to obtain the most recent observation in a group?

adhunamukherjee — Sun, 06 Dec 2020 07:06:16 GMT

Hello SAS Communities,

I have the following dataset HypTabs.Vitals:

Obs SSN VisitDt Ht Wt SBP DBP

000-00-0001

13JAN2010

149

117

000-00-0001

21JAN2011

149

117

000-00-0001

13JAN2012

148

121

000-00-0001

22JAN2013

153

111

000-00-0001

15JAN2014

152

116

000-00-0002

14JUN2010

141

118

000-00-0002

20JUN2012

140

118

000-00-0002

17JUN2013

138

117

000-00-0002

17JUN2014

138

111

000-00-0003

03JAN2011

149

120

000-00-0003

02JAN2012

151

111

000-00-0003

31DEC2012

152

126

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

000-00-0001

15JAN2014

152

116

000-00-0002

17JUN2014

138

111

000-00-0003

31DEC2012

152

126

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

Thanks,
Adhuna Mukhopadhyay

Re: How to obtain the most recent observation in a group?

Kurt_Bremser — Sun, 06 Dec 2020 07:38:14 GMT

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;

Re: How to obtain the most recent observation in a group?

mdakkak — Sun, 06 Dec 2020 20:08:31 GMT

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!

Re: How to obtain the most recent observation in a group?

bfreeman0402 — Wed, 16 Dec 2020 05:48:42 GMT

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