topic Re: Choose the last observations in the same firm-year in SAS Programming

Choose the last observations in the same firm-year

joon1 — Fri, 13 Sep 2019 21:57:34 GMT

Hi Madam/Sir,

I would like to choose the last observation out of two observations in the same firm-year. For instance, I would like to choose observations # 2, 4, and 6 and drop observations #1,2, and 3. It will be highly appreciative if you can provide the programming code. Thank you. Joon1

Obs CIKCODE cyear ANNUALREPORTDATE dirind123456

0000002024	2016	20160101	10
0000002024	2016	20161201	11
0000010254	2014	20140301	3
0000010254	2014	20141201	5
0000018169	2011	20110101	4
0000018169	2011	20111201	5

Re: Choose the last observations in the same firm-year

Reeza — Fri, 13 Sep 2019 22:04:27 GMT

proc sort data=have;
by cikcode cyear annualReportdate;
run;

data want;
set have;
by cikcode cyear annualreportdate;
if last.cyear;
run;

@joon1 wrote:

Hi Madam/Sir,

I would like to choose the last observation out of two observations in the same firm-year. For instance, I would like to choose observations # 2, 4, and 6 and drop observations #1,2, and 3. It will be highly appreciative if you can provide the programming code. Thank you. Joon1

Obs CIKCODE cyear ANNUALREPORTDATE dirind123456

0000002024 2016 20160101 10

0000002024 2016 20161201 11

0000010254 2014 20140301 3

0000010254 2014 20141201 5

0000018169 2011 20110101 4

0000018169 2011 20111201 5

Re: Choose the last observations in the same firm-year

ballardw — Fri, 13 Sep 2019 22:05:29 GMT

if the data are in order as your example implies:

Data want;
   set have;
   by cikcode cyear;
   if last.cyear;
run;

Re: Choose the last observations in the same firm-year

hashman — Sat, 14 Sep 2019 22:26:37 GMT

@joon1:

If your file is already properly sorted, just use the DATA step posted by @ballardw and @Reeza.

If it's not sorted, a single-step solution can be:

data have ;                                                                                                                                                                                                                                                     
  input CIKCODE cyear ANNUALREPORTDATE dirind123456 ;                                                                                                                                                                                                           
  cards ;                                                                                                                                                                                                                                                       
0000002024  2016  20160101  10                                                                                                                                                                                                                                  
0000002024  2016  20161201  11                                                                                                                                                                                                                                  
0000010254  2014  20140301   3                                                                                                                                                                                                                                  
0000010254  2014  20141201   5                                                                                                                                                                                                                                  
0000018169  2011  20110101   4                                                                                                                                                                                                                                  
0000018169  2011  20111201   5                                                                                                                                                                                                                                  
;                                                                                                                                                                                                                                                               
run ;                                                                                                                                                                                                                                                           
                                                                                                                                                                                                                                                                
data _null_ ;                                                                                                                                                                                                                                                   
  dcl hash h (dataset:"have", duplicate:"r", ordered:"a") ;                                                                                                                                                                                                     
  h.definekey ("cikcode", "cyear") ;                                                                                                                                                                                                                            
  h.definedata (all:"y") ;                                                                                                                                                                                                                                      
  h.definedone () ;                                                                                                                                                                                                                                             
  h.output (dataset:"want") ;                                                                                                                                                                                                                                   
  stop ;                                                                                                                                                                                                                                                        
  set have ;                                                                                                                                                                                                                                                    
run ;

This assumes that you have enough memory to hold the output data set, which for the sheet in your Excel workbook is guaranteed. But if in reality you have a much bigger file, here is another version requiring the hash table to hold only the record identifier in its data portion (rather than all the non-key variables):

data want ;                                                                                                                                                                                                                                                     
  dcl hash h (ordered:"a") ;                                                                                                                                                                                                                                    
  h.definekey ("cikcode", "cyear") ;                                                                                                                                                                                                                            
  h.definedata ("rid") ;                                                                                                                                                                                                                                        
  h.definedone () ;                                                                                                                                                                                                                                             
  do rid = 1 by 1 until (z) ;                                                                                                                                                                                                                                   
    set have end = z ;                                                                                                                                                                                                                                          
    h.replace() ;                                                                                                                                                                                                                                               
  end ;                                                                                                                                                                                                                                                         
  dcl hiter hi ("h") ;                                                                                                                                                                                                                                          
  do while (hi.next() = 0) ;                                                                                                                                                                                                                                    
    set have point = rid ;                                                                                                                                                                                                                                      
    output ;                                                                                                                                                                                                                                                    
  end ;                                                                                                                                                                                                                                                         
run ;

Suit yourself.

Kind regards

Paul D.