BookmarkSubscribeRSS Feed
acil
Calcite | Level 5

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

2 REPLIES 2
Reeza
Super User

Use the SQL max function. 

 

Proc sql;

create table want as

select cusip, max(ceodate) as ceodate, dirdate

from have

group by cusip;

quit;

Ksharp
Super User
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 665 views
  • 0 likes
  • 3 in conversation