Solved: Select last quarter data using last.

joon1

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

Tom

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.

Tom

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.

Select last quarter data using last.