Obsidian | Level 7

SAS can not identify last.month

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

1 ACCEPTED SOLUTION

Accepted Solutions
Diamond | Level 26

Re: SAS can not identify last.month

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;```

2 REPLIES 2
Diamond | Level 26

Re: SAS can not identify last.month

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

Re: SAS can not identify last.month

``````data want;
set have;
by stocks month_year notsorted ;
if last.month_year then output;
run;``````
Discussion stats
• 2 replies
• 312 views
• 0 likes
• 3 in conversation