Dear all,
I have data that I want to keep the first date of every month for every PERMNO. I donot think intnx can do this because all date in one month will change into first date of month, I did try first.date (month, year) but the table still one have 1 observation for 1 permno.
So could you help me with this? Thanks.
PERMNO | end | sum | month | year | |
10006 | 2/01/1926 | 0.02055 | 1 | 1926 | keep |
10006 | 4/01/1926 | 0.007088 | 1 | ||
10006 | 5/01/1926 | -0.01259 | 1 | ||
10006 | … | 1 | |||
10006 | 30/01/1926 | -0.02597 | 1 | ||
10006 | 1/02/1926 | -0.02574 | 2 | keep | |
10006 | 2/02/1926 | -0.01599 | 2 | ||
10006 | … | ||||
10006 | 1/03/1926 | 0.111675 | 3 | keep | |
10006 | 2/03/1926 | 0.113096 | 3 | ||
10006 | …. | ||||
10006 | 12 | ||||
10006 | 3/01/1927 | 0.15162 | 1 | 1927 | keep |
10006 | 4/01/1927 | 0.161668 | |||
10006 | 5/01/1927 | 0.159616 | |||
88888 | 2/01/1926 | 0.02055 | 1 | 1926 | keep |
88888 | 4/01/1926 | 0.007088 | 1 | ||
88888 | 5/01/1926 | -0.01259 | 1 | ||
88888 | … | … | |||
88888 | |||||
88888 | 1/02/1926 | -0.02574 | 2 | keep | |
88888 | 2/02/1926 | -0.01599 | 2 | ||
88888 | … | ||||
88888 | 1/03/1926 | 0.111675 | 3 | keep | |
88888 | 2/03/1926 | 0.113096 | 3 |
data have;
input PERMNO end :ddmmyy10. sum;
format end ddmmyy10.;
month = month(end);year = year(end);
datalines;
10006 2/01/1926 0.02055
10006 4/01/1926 0.007088
10006 5/01/1926 -0.01259
10006 30/01/1926 -0.02597
10006 1/02/1926 -0.02574
10006 2/02/1926 -0.01599
10006 1/03/1926 0.111675
10006 2/03/1926 0.113096
10006 3/01/1927 0.15162
10006 4/01/1927 0.161668
10006 5/01/1927 0.159616
88888 2/01/1926 0.02055
88888 4/01/1926 0.007088
88888 5/01/1926 -0.01259
88888 1/02/1926 -0.02574
88888 2/02/1926 -0.01599
88888 1/03/1926 0.111675
88888 2/03/1926 0.113096
;
proc sort data = have;
by permno year month;
run;
data want;
set have;
by permno year month;
if first.month;
run;
Sort appropriately and use FIRST.
data want;
set have;
by permno year month;
if first.month;
run;
data have;
input PERMNO end :ddmmyy10. sum;
format end ddmmyy10.;
month = month(end);year = year(end);
datalines;
10006 2/01/1926 0.02055
10006 4/01/1926 0.007088
10006 5/01/1926 -0.01259
10006 30/01/1926 -0.02597
10006 1/02/1926 -0.02574
10006 2/02/1926 -0.01599
10006 1/03/1926 0.111675
10006 2/03/1926 0.113096
10006 3/01/1927 0.15162
10006 4/01/1927 0.161668
10006 5/01/1927 0.159616
88888 2/01/1926 0.02055
88888 4/01/1926 0.007088
88888 5/01/1926 -0.01259
88888 1/02/1926 -0.02574
88888 2/02/1926 -0.01599
88888 1/03/1926 0.111675
88888 2/03/1926 0.113096
;
proc sort data = have;
by permno year month;
run;
data want;
set have;
by permno year month;
if first.month;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.