Select last quarter data using last.

joon1 — Mon, 17 Jun 2024 23:21:53 GMT

Dear Madam/Sir,

The data set is as follows:

Obs rdate cusip ticker held_pct cyear1234567891011121314151617181920

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

Re: Select last quarter data using last.

Tom — Tue, 18 Jun 2024 00:31:40 GMT

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 — Tue, 18 Jun 2024 01:50:21 GMT

Thank you so much, Tom. Appreciated.