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!
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;
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;
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.