BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JacAder
Obsidian | Level 7

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Satish_Parida
Lapis Lazuli | Level 10

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

View solution in original post

4 REPLIES 4
unison
Lapis Lazuli | Level 10

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;
-unison
Satish_Parida
Lapis Lazuli | Level 10

@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
Lapis Lazuli | Level 10

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

-unison
JacAder
Obsidian | Level 7

Thank you guys for your kindly assistance!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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