SAS can not identify last.month

[trungcva112]

Hi everyone,

I have a data structured like this, where dates are trading days and month_year is a character variable created by this function:

month_year=put(date,monyy7.);

Stocks	date	Month_year
……………..	……………..	……………
ABC	01/04/2011	Apr2011
ABC	04/04/2011	Apr2011
ABC	05/04/2011	Apr2011
ABC	06/04/2011	Apr2011
ABC	………………………	Apr2011
ABC	28/04/2011	Apr2011
ABC	29/04/2011	Apr2011
ABC	02/05/2011	May2011
ABC	03/05/2011	May2011
ABC	………………………………	May2011
ABC	31/05/2011	May2011
XYZ	02/12/2010	Dec2010
XYZ	03/12/2010	Dec2010
XYZ	05/12/2010	Dec2010
XYZ	06/12/2010	Dec2010
………..	…………..	Dec2010
XYZ	30/12/2010	Dec2010
XYZ	03/01/2011	Jan2011
XYZ	04/01/2011	Jan2011
XYZ	05/01/2011	Jan2011
XYZ	06/01/2011	Jan2011
………	………	…………..
XYZ	29/01/2011	Jan2011

Now I want to identify the last trading day each month for each stock. The output should look like this, where dates are last trading date of each month:

Stocks	Date	Month_year
………..	……….	…………
ABC	28/04/2011	Apr2011
ABC	31/05/2011	May2011
………..	……….	………..
XYZ	30/12/2010	Dec2010
XYZ	29/01/2011	Jan2011

I use this code:

data want;
set have;
 by stocks date month_year;
     if last.month_year then output;
  run;

However, this code produce the original data (with all the dates within month). That means SAS identify every observations in the month_year column as last.month_year. This is hard to understand because I convert the month_year to character.

Could anyone show me how to fix it? Thank you for your time

[RW9 · Accepted solution]

Post test data in the form of a datastep!

This:

by stocks date month_year;

Sets up a flag in the data based on month_year being a subgroup of date and as date is unique for each row, each row's month_year is both the first and last within the date value.

What would be a better method:

proc sort data=yourdata;
  by stocks month_year descending date;
run;
proc sort data=yourdata nodupkey;
  by stocks month_year;
run;

This will order the data so the last date is first per stocks group month_year, then the second sort takes the first row from each group.  You can do it with first/last, do the sort as given first step, then first.month_year on the result, e.g

proc sort data=yourdata;
  by stocks month_year descending date;
run;
data want;
  set yourdata;
  by stocks month_year;
  if first.month_year then output;
run;

[Ksharp]

data want;
set have;
 by stocks month_year notsorted ;
     if last.month_year then output;
  run;