SAS can not identify last.month

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

SAS can not identify last.month

[ Edited ]

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.);

 

StocksdateMonth_year
……………..……………..……………
ABC01/04/2011Apr2011
ABC04/04/2011Apr2011
ABC05/04/2011Apr2011
ABC06/04/2011Apr2011
ABC………………………Apr2011
ABC28/04/2011Apr2011
ABC29/04/2011Apr2011
ABC02/05/2011May2011
ABC03/05/2011May2011
ABC………………………………May2011
ABC31/05/2011May2011
XYZ02/12/2010Dec2010
XYZ03/12/2010Dec2010
XYZ05/12/2010Dec2010
XYZ06/12/2010Dec2010
………..…………..Dec2010
XYZ30/12/2010Dec2010
XYZ03/01/2011Jan2011
XYZ04/01/2011Jan2011
XYZ05/01/2011Jan2011
XYZ06/01/2011Jan2011
…………………………..
XYZ29/01/2011Jan2011

 

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:

StocksDateMonth_year
………..……….…………
ABC28/04/2011Apr2011
ABC31/05/2011May2011
………..……….………..
XYZ30/12/2010Dec2010
XYZ29/01/2011Jan2011

 

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


Accepted Solutions
Solution
2 weeks ago
Super User
Super User
Posts: 8,634

Re: SAS can not identify last.month

Posted in reply to trungcva112

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;

 

View solution in original post


All Replies
Solution
2 weeks ago
Super User
Super User
Posts: 8,634

Re: SAS can not identify last.month

Posted in reply to trungcva112

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;

 

Super User
Posts: 10,325

Re: SAS can not identify last.month

Posted in reply to trungcva112
data want;
set have;
 by stocks month_year notsorted ;
     if last.month_year then output;
  run;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 81 views
  • 0 likes
  • 3 in conversation