keep first or last observation by group

[JacAder]

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;

[unison]

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;

[Satish_Parida · Accepted solution]

@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;

[unison]

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

[JacAder]

Thank you guys for your kindly assistance!