topic Re: CODING FOR FIRST VARIABLE BY GROUP in SAS Programming

CODING FOR FIRST VARIABLE BY GROUP

HEB1 — Thu, 23 Mar 2023 08:58:54 GMT

my data looks like that:

id year FD REPORTING
a 1999 0 0
a 2000 0 0
a 2001 1 0
a 2002 1 0
a 2003 0 1
a 2004 0 1
a 2005 0 1
b 2001 0 0
b 2002 0 0
b 2003 0 0
b 2004 0 1
b 2005 0 1
c 1998 0 0
c 1999 0 0
c 2000 1 0
c 2001 0 0
c 2002 0 0

I want to create new variables that equal 1 only for the first reporting=1 and first fd=1. Meaning- I want my data to look like that:

id year FD REPORTING NEW_FD NEW_REPORTING
a 1999 0 0 0 0
a 2000 0 0 0 0
a 2001 1 0 1 0
a 2002 1 0 0 0
a 2003 0 1 0 1
a 2004 0 1 0 0
a 2005 0 1 0 0
b 2001 0 0 0 0
b 2002 0 0 0 0
b 2003 0 0 0 0
b 2004 0 1 0 1
b 2005 0 1 0 0
c 1998 0 0 0 0
c 1999 0 0 0 0
c 2000 1 0 1 0
c 2001 0 0 0 0
c 2002 0 0 0 0

Thanks!

Re: CODING FOR FIRST VARIABLE BY GROUP

s_lassen — Thu, 23 Mar 2023 09:27:27 GMT

It is easier if you post your example data as a datastep, e.g. like this:

data have;                                                                                                                              
input id $ year FD REPORTING;                                                                                                           
cards;                                                                                                                                  
a 1999 0 0                                                                                                                              
a 2000 0 0                                                                                                                              
a 2001 1 0                                                                                                                              
a 2002 1 0                                                                                                                              
a 2003 0 1                                                                                                                              
a 2004 0 1                                                                                                                              
a 2005 0 1                                                                                                                              
b 2001 0 0                                                                                                                              
b 2002 0 0                                                                                                                              
b 2003 0 0                                                                                                                              
b 2004 0 1                                                                                                                              
b 2005 0 1                                                                                                                              
c 1998 0 0                                                                                                                              
c 1999 0 0                                                                                                                              
c 2000 1 0                                                                                                                              
c 2001 0 0                                                                                                                              
c 2002 0 0                                                                                                                              
;run;

I think you can get what you want like this:

data want;                                                                                                                              
  _FD=0;                                                                                                                                
  _report=0;                                                                                                                            
  do until(last.id);                                                                                                                    
    set have;                                                                                                                           
    by id;                                                                                                                              
    if fd and not _fd then do;                                                                                                          
      NEW_FD=1;                                                                                                                         
      _FD=1;                                                                                                                            
      end;                                                                                                                              
    else NEW_FD=0;                                                                                                                      
    if reporting and not _report then do;                                                                                               
      NEW_REPORTING=1;                                                                                                                  
      _report=1;                                                                                                                        
      end;                                                                                                                              
    else NEW_REPORTING=0;                                                                                                               
    output;                                                                                                                             
    end;                                                                                                                                
drop _:;                                                                                                                                
run;

Re: CODING FOR FIRST VARIABLE BY GROUP

HEB1 — Thu, 23 Mar 2023 09:38:15 GMT

It works great! Many thanks 😉