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