Help using Base SAS procedures

Filling data based on provided data in the same coloumn

Reply
Occasional Contributor
Posts: 8

Filling data based on provided data in the same coloumn

Hi..

I need a help to complete Ceodate with the maximum year of the same cusip, when there is more than one year number for one cusip.

my_data

CUSIP Ceodate Dirdate
00846U101   2003
00846U101   2000
00846U101 2003  
013817101   2002
013817101   2002
013817101 2002  
013817101 2005  
013817101   1988
013817101   1977
013817101   1991
013817101   1994
013817101 1998  
00949P108   2006
00949P108   2004
00949P108   2001
00949P108 2005  
00949P108   1993
00949P108   1994
00949P108   2001
00949P108 1999  

 the output that I need is more likely as follows:

CUSIP Ceodate Dirdate
00846U101 2003 2003
00846U101 2003 2000
00846U101 2003  
013817101 2005 2002
013817101 2005 2002
013817101 2002  
013817101 2005  
013817101 2005 1988
013817101 2005 1977
013817101 2005 1991
013817101 2005 1994
013817101 1998  
00949P108 2005 2006
00949P108 2005 2004
00949P108 2005 2001
00949P108 2005  
00949P108 2005 1993
00949P108 2005 1994
00949P108 2005 2001
00949P108 1999  

thanks in advance

Super User
Posts: 17,961

Re: Filling data based on provided data in the same coloumn

Use the SQL max function. 

 

Proc sql;

create table want as

select cusip, max(ceodate) as ceodate, dirdate

from have

group by cusip;

quit;

Super User
Posts: 9,691

Re: Filling data based on provided data in the same coloumn

data have;
infile cards expandtabs truncover;
input CUSIP	: $20. Ceodate	Dirdate;
cards;
00846U101	. 	2003
00846U101	 .	2000
00846U101	2003	 
013817101	 .	2002
013817101	 .	2002
013817101	2002	 
013817101	2005	 
013817101	 .	1988
013817101	 .	1977
013817101	 .	1991
013817101	 .	1994
013817101	1998	 
00949P108	 .	2006
00949P108	 .	2004
00949P108	 .	2001
00949P108	2005	 
00949P108	 .	1993
00949P108	 .	1994
00949P108	 .	2001
00949P108	1999	
00949P108	.
00949P1081	.
00949P1081	.
;
run;
data temp;
 set have;
 by CUSIP notsorted;
 n+1;
 retain new_Ceodate;
 if first.CUSIP then call missing(new_Ceodate);
 if not missing(Ceodate) then new_Ceodate=Ceodate;
 drop Ceodate;
run;
proc sort data=temp;
 by descending n;
run;
data want;
 set temp;
 by CUSIP notsorted;
 retain Ceodate;
 if first.CUSIP then call missing(Ceodate);
 if not missing(new_Ceodate) then Ceodate=new_Ceodate;
 drop new_Ceodate;
run;
proc sort data=want;
 by n;
run;
Ask a Question
Discussion stats
  • 2 replies
  • 164 views
  • 0 likes
  • 3 in conversation