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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.