topic Re: keep the first observation by firm and year in SAS Programming

keep the first observation by firm and year

Nieves — Sun, 02 Oct 2022 09:23:14 GMT

Hi SAS experts,

I would like to get the first observation by firm id and year id. May I seek your advice on a good way to do so ?

The first.firm only keeps the first observation by firm id.

For example, in the following data, I would like to keep the bold observation, which is the first observation by firm and year

Firm	Year	Return
1	2000	23
1	2001	34
1	2002	45
2	1999	56
2	2000	67
2	2001	78
2	2002	89
3	2004	90
3	2005	67

Thanks

Re: keep the first observation by firm and year

Kurt_Bremser — Sun, 02 Oct 2022 09:55:00 GMT

proc sort data=have;
by firm year;
run;

data want;
set have;
by firm;
if first.firm;
run;

Re: keep the first observation by firm and year

Nieves — Sun, 02 Oct 2022 11:28:46 GMT

Thanks for your reply!

May I seek your advice on how to update the code in order to keep the following bold data ? that is, the first observation of a firm in a year

Firm	Year	Month
1	2000	1
1	2000	2
1	2000	3
1	2001	1
1	2001	2
1	2001	3
1	2002	1
1	2002	2
2	2000	1
2	2000	2
2	2001	1
2	2001	2
2	2002	1
2	2002	2

Re: keep the first observation by firm and year

ballardw — Sun, 02 Oct 2022 11:46:55 GMT

Exact same logic only use

data want; set have; by firm year; if first.year; run;

By group processing a data step adds automatic variables First. and Last. for each variable that appears on the BY statement. These are numeric 0/1 values and SAS will use the 1 as "true" and 0 for "false". So when the data is in the correct order you can test membership of the combinations of all the variables on the By statement if needed.

Re: keep the first observation by firm and year

Kurt_Bremser — Sun, 02 Oct 2022 19:16:50 GMT

Just a very small expansion of my code:

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

data want;
set have;
by firm year;
if first.year;
run;