topic Filling data based on provided data in the same coloumn in SAS Procedures

Filling data based on provided data in the same coloumn

acil — Thu, 14 Jul 2016 06:17:59 GMT

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

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

Reeza — Thu, 14 Jul 2016 06:25:31 GMT

Use the SQL max function.

Proc sql;

create table want as

select cusip, max(ceodate) as ceodate, dirdate

from have

group by cusip;

quit;

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

Ksharp — Thu, 14 Jul 2016 07:18:13 GMT

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;