topic Re: deleting first obervation by a Variable condition in SAS Programming

deleting first obervation by a Variable condition

sms1891 — Fri, 11 Jan 2019 19:45:08 GMT

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

3 0 Keep

3 1 Keep

4 1 Keep

5 1 Keep

6 0 Remove

6 0 Keep

6 1 Keep

Thank you for the help in advance!

Re: deleting first obervation by a Variable condition

franriv — Fri, 11 Jan 2019 19:54:37 GMT

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;

Re: deleting first obervation by a Variable condition

novinosrin — Fri, 11 Jan 2019 19:55:49 GMT



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;

Re: deleting first obervation by a Variable condition

novinosrin — Fri, 11 Jan 2019 20:12:13 GMT

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;

Re: deleting first obervation by a Variable condition

novinosrin — Fri, 11 Jan 2019 20:22:59 GMT

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;