BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
trungcva112
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User
data want;
set have;
 by stocks month_year notsorted ;
     if last.month_year then output;
  run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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