DATA Step, Macro, Functions and more

Keep first date of each month

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Keep first date of each month

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.  

 

PERMNOendsummonthyear 
100062/01/19260.0205511926keep
100064/01/19260.0070881  
100065/01/1926-0.012591  
10006 1  
1000630/01/1926-0.025971  
100061/02/1926-0.025742 keep
100062/02/1926-0.015992  
10006    
100061/03/19260.1116753 keep
100062/03/19260.1130963  
10006  ….  
10006  12  
100063/01/19270.1516211927keep
100064/01/19270.161668   
100065/01/19270.159616   
888882/01/19260.0205511926keep
888884/01/19260.0070881  
888885/01/1926-0.012591  
88888   
88888     
888881/02/1926-0.025742 keep
888882/02/1926-0.015992  
88888    
888881/03/19260.1116753 keep
888882/03/19260.1130963  

Accepted Solutions
Solution
‎11-23-2016 03:27 AM
PROC Star
Posts: 733

Re: Keep first date of each month

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;

View solution in original post


All Replies
Super User
Posts: 19,770

Re: Keep first date of each month

Sort appropriately and use FIRST. 

 

data want;

set have;

by permno year month;

 

if first.month;

run;

Solution
‎11-23-2016 03:27 AM
PROC Star
Posts: 733

Re: Keep first date of each month

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;
Occasional Contributor
Posts: 6

Re: Keep first date of each month

Thank you very much, it works.


##- Please type your reply above this line. Simple formatting, no
attachments. -##
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 193 views
  • 2 likes
  • 3 in conversation