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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: