Select last quarter data using last.

[joon1]

Dear Madam/Sir,

The data set is as follows:

Obs rdate cusip ticker held_pct cyear1234567891011121314151617181920

03/31/2021	00032Q10	AADI	0.04381	2021
09/30/2021	00032Q10	AADI	0.09191	2021
12/31/2021	00032Q10	AADI	0.07964	2021
03/31/2022	00032Q10	AADI	0.07854	2022
06/30/2022	00032Q10	AADI	0.07849	2022
09/30/2022	00032Q10	AADI	0.07821	2022
12/31/2022	00032Q10	AADI	0.05440	2022
03/31/2023	00032Q10	AADI	0.01302	2023
03/31/1999	00036020	AAON	0.14898	1999
06/30/1999	00036020	AAON	0.16782	1999
09/30/1999	00036020	AAON	0.17431	1999
12/31/1999	00036020	AAON	0.17496	1999
03/31/2003	00036020	AAON	0.00002	2003
06/30/2003	00036020	AAON	0.00005	2003
09/30/2003	00036020	AAON	0.00010	2003
12/31/2003	00036020	AAON	0.00012	2003
03/31/2004	00036020	AAON	0.00012	2004
06/30/2004	00036020	AAON	0.00012	2004
09/30/2004	00036020	AAON	0.00013	2004
12/31/2004	00036020	AAON	0.00012	2004

I tried to select last quarter data from quarterly data using 'last.' function as follows:

data i2; set i1; by cusip rdate;
q4=last.rdate; run;

However, q4  is assigned for all observations. 

And the following code does not work either.

data i2; set i1; by cusip rdate;
if last.rdate; run;

NOTE: There were 213968 observations read from the data set WORK.IS2.
NOTE: The data set WORK.IS3 has 213968 observations and 5 variables.

The SAS instructions online does not work well. Any help will be highly appreciated.

Sincerely,

Joon1

[Tom · Accepted solution]

You seem to only have one observation per DATE value.  So every observations is going to be both FIRST.DATE and LAST.DATE.

You would need to have YEAR variable.  Then if you have 4 quarters per YEAR you could use LAST.YEAR to select the last available QTR for the year.

data for_analysis;
  set have;
  year=year(RDATE);
run;
data want;
  set have;
  by cusip year rdate;
  if last.year;
run;

The first data step could be a VIEW if the data is large.

[joon1]

Thank you so much, Tom. Appreciated.