topic Re: keep first or last observation by group in SAS Programming

keep first or last observation by group

JacAder — Sat, 01 Feb 2020 00:33:49 GMT

I would like to keep the first or last observations for different dategroups:

*for each ID in each year-month, keep the FIRST observation if dategroup=BEG;

*for each ID in each year-month, keep the LAST observation if dategroup=END;

The idea is as following, how to make the code works? appreciated!

output will be like:

ID year month value dategroup

AA 2001 1 11 BEG

AA 2001 1 23 END

BB 2003 2 31 BEG

CC 2005 3 43 END

data have;
input ID $3. year month value dategroup $3.;
datalines;
AA 2001 1 11 BEG
AA 2001 1 12 BEG
AA 2001 1 13 BEG
AA 2001 1 21 END
AA 2001 1 22 END
AA 2001 1 23 END
BB 2003 2 31 BEG
BB 2003 2 32 BEG
BB 2003 2 33 BEG
CC 2005 3 41 END
CC 2005 3 42 END
CC 2005 3 43 END
run;

data want;
set have;
*for each ID in each year-month, keep the FIRST observation if dategroup=BEG;
if dategroup="BEG" then do;
by ID year month;
if first.month then output;
end;
*for each ID in each year-month, keep the LAST observation if dategroup=END;
if dategroup="END" then do;
by ID year month;
if last.month then output;
end;
run;

Re: keep first or last observation by group

unison — Mon, 03 Feb 2020 05:24:40 GMT

You need to apply by group processing on id and dategroup. Select the first.dategroup & dategroup='BEG' combination as well as the last.dategroup & dategroup='END' combination:

data want;
	set have;
	by id dategroup;

	if (dategroup='BEG')*(first.dategroup) or (dategroup='END')*(last.dategroup);
run;

Re: keep first or last observation by group

Satish_Parida — Mon, 03 Feb 2020 07:44:15 GMT

@unison Small Correction may be

proc sort data=have;
by ID year month;
run;

data want;
set have;
by ID year month;
if (dategroup='BEG')*(first.month) or (dategroup='END')*(last.month);
run;

Re: keep first or last observation by group

unison — Mon, 03 Feb 2020 12:39:56 GMT

Yes, to be safe. Looks like dategroup was flagged based on id, year and month.

Re: keep first or last observation by group

JacAder — Fri, 07 Feb 2020 05:41:18 GMT

Thank you guys for your kindly assistance!