topic Dropping all observations of an ID if first meets condition in SAS Programming

Dropping all observations of an ID if first meets condition

hwkim286 — Wed, 14 Aug 2019 06:33:40 GMT

Hello.

I am currently trying to exclude all observations within an ID if the first observation of a variable is larger than 0.

So for example,

PID Gender Income Year Exercise

1 1 300 2000 0

1 1 400 2002 1

1 1 50001 2004 2

2 2 90000 2000 4

2 2 5999 2002 10

2 2 3443 2004 0

3 2 1333 2000 2

3 2 4000 2002 3

3 2 5000 2004 10

For PID 2 and 3, since exercise at year 2000 (the first year), I would like to delete everything under PID 2 and 3 (including the IDs) so that they won't show as having missing values.

Thank you.

Re: Dropping all observations of an ID if first meets condition

PeterClemmensen — Wed, 14 Aug 2019 06:58:18 GMT

I assume that your data is sorted by PID and Year

data have;
input PID Gender Income Year Exercise;
datalines;
1 1 300 2000 0
1 1 400 2002 1
1 1 50001 2004 2
2 2 90000 2000 4
2 2 5999 2002 10
2 2 3443 2004 0 
3 2 1333 2000 2
3 2 4000 2002 3
3 2 5000 2004 10
;

data want(drop=dummy);
    dummy=0;
    do until (last.PID);
        set have;
        by PID;
        if first.PID & Exercise gt 0 then dummy=1;
    end;
    do until (last.PID);
        set have;
        by PID;
        if dummy=0 then output;
    end;
run;

Re: Dropping all observations of an ID if first meets condition

hashman — Wed, 14 Aug 2019 07:03:50 GMT

@hwkim286 :

Since you condition is "first observation" for each ID, it's a single-pass job:

data have ;                                        
  input PID Gender Income Year Exercise ;          
  cards ;                                          
1  1    300  2000   0                              
1  1    400  2002   1                              
1  1  50001  2004   2                              
2  2  90000  2000   4                              
2  2   5999  2002  10                              
2  2   3443  2004   0                              
3  2   1333  2000   2                              
3  2   4000  2002   3                              
3  2   5000  2004  10                              
run ;                                              
                                                   
data want ;                                        
  set have ;                                       
  by pid ;                                         
  if first.pid then _iorc_ = ifn (exercise, 0, 1) ;
  if _iorc_ ;                                      
run ;

Kind regards

Paul D.

Re: Dropping all observations of an ID if first meets condition

Jakub_O — Tue, 28 Jan 2020 14:59:48 GMT

Please note that this condition

  if first.pid then _iorc_ = ifn (exercise, 0, 1) ;

will not work as specified in the initial post if the variable exercise takes negative values.

To work around that the condition should be

  if first.pid then _iorc_ = ifn (exercise gt 0, 0, 1) ;