deleting first obervation by a Variable condition

[sms1891]

Hi all I need help in deleting first observation only if a particular ID is seen at Location 0 more than one time. If any ID has just one observation at Location =0 then, I need to keep it (for example ID #2).  I have to keep all observations at Location = 1. If any ID has 3 entries for Location = 0 then, I need to keep the last one and remove the first two (although I may not have any of this situations). Any ID with just Location = 1 will also be retained irrespective of the number of times it occurs. I have indicated the observations that I want to keep under Status.

ID                      Location   Status

1                         0              Remove

1                         0              Keep

1                         1             Keep

2                         0             Keep

2                          1           Keep

2                         1            Keep

3                          0         Keep

3                          1         Keep

4                          1            Keep

4                          1          Keep

4                          1            Keep

5                          1            Keep

6                          0        Remove

6                          0       Remove

6                         0           Keep

6                        1          Keep

6                           1     Keep

Thank you for the help in advance!

[franriv · Accepted solution]

this will do:

data test;
	input id location status $;
	datalines;
1 0 Remove
1 0 Keep
1 1 Keep
2 0 Keep
2 1 Keep
2 1 Keep
3 0 Keep
3 1 Keep
4 1 Keep
4 1 Keep
4 1 Keep
5 1 Keep
6 0 Remove
6 0 Remove
6 0 Keep
6 1 Keep
6 1 Keep
;
run;

proc sort data=test; by id location;

data test;
	set test;
	by id location;
	if not (location eq 0 and not last.location);
run;

[novinosrin]

data have;
input ID                      Location   ;
cards;
1                         0              Remove
1                         0              Keep
1                         1             Keep
2                         0             Keep
2                          1           Keep
2                         1            Keep
3                          0         Keep
3                          1         Keep
4                          1            Keep
4                          1          Keep
4                          1            Keep
5                          1            Keep
6                          0        Remove
6                          0       Remove
6                         0           Keep
6                        1          Keep
6                           1     Keep
;
 
proc sql;
create table want as
select distinct *
from have(where=( location=0))
union all
select  *
from have
where location ne 0
order by id,location;
quit;

[novinosrin]

Hash fun:

data have;
input ID                      Location   ;
cards;
1                         0              Remove
1                         0              Keep
1                         1             Keep
2                         0             Keep
2                          1           Keep
2                         1            Keep
3                          0         Keep
3                          1         Keep
4                          1            Keep
4                          1          Keep
4                          1            Keep
5                          1            Keep
6                          0        Remove
6                          0       Remove
6                         0           Keep
6                        1          Keep
6                           1     Keep
;
data _null_;
if _n_=1 then do;
 dcl hash H (ordered: "A",multidata:'y') ;
   h.definekey  ("id","location") ;
   h.definedone () ;
end;
set have end=lr;
if location=0 then h.replace();else h.add();
if lr;
h.output(dataset:'want');
run;
 

[novinosrin]

And More Hash Fun:

data have;
input ID                      Location   ;
cards;
1                         0              Remove
1                         0              Keep
1                         1             Keep
2                         0             Keep
2                          1           Keep
2                         1            Keep
3                          0         Keep
3                          1         Keep
4                          1            Keep
4                          1          Keep
4                          1            Keep
5                          1            Keep
6                          0        Remove
6                          0       Remove
6                         0           Keep
6                        1          Keep
6                           1     Keep
;
data _null_;
if _n_=1 then do;
 dcl hash H (dataset:'have(where=(location=0))',duplicate:'r',ordered: "A",multidata:'y') ;
   h.definekey  ("id","location") ;
   h.definedone () ;
end;
set have(where=(location ne 0)) end=lr;
rc=h.add();
if lr;
h.output(dataset:'want');
run;