Hello,
I am wondering if there is a way to use an 'IF THEN' statement where one record can meet the criteria to include the full group. In this example the criteria is if the person is in the USA at any point in the dataset then all of their other records can be included. So for Person A since they were in USA, all 8 records would be included. For person B since they weren't in USA, no records would be included.
person | year | fiscal period | # of vacations | location |
A | 2013 | 1 | 0 | Canada |
A | 2013 | 2 | 0 | USA |
A | 2013 | 3 | 0 | Canada |
A | 2013 | 4 | 0 | Canada |
A | 2014 | 1 | 0 | Canada |
A | 2014 | 2 | 0 | Canada |
A | 2014 | 3 | 1 | Canada |
A | 2014 | 4 | 0 | Canada |
B | 2013 | 1 | 0 | Canada |
B | 2013 | 2 | 0 | Canada |
B | 2013 | 3 | 0 | Canada |
B | 2013 | 4 | 0 | Canada |
B | 2014 | 1 | 0 | Canada |
B | 2014 | 2 | 0 | Canada |
B | 2014 | 3 | 0 | Canada |
B | 2014 | 4 | 0 | Canada |
It is easy to be done by sql:
proc sql;
create table want as select *
from have where person in
(select person from have where location = 'USA')
; quit;
otherwise you need two steps:
1) subset dataset where location = 'USA'
2) merge have with the subset by person
data want;
merge
have (
in=h
keep=person location
where=(location = 'USA')
)
have
;
by person;
if h;
run;
data have;
input person $ year fiscal_period vacations location $;
cards;
A 2013 1 0 Canada
A 2013 2 0 USA
A 2013 3 0 Canada
A 2013 4 0 Canada
A 2014 1 0 Canada
A 2014 2 0 Canada
A 2014 3 1 Canada
A 2014 4 0 Canada
B 2013 1 0 Canada
B 2013 2 0 Canada
B 2013 3 0 Canada
B 2013 4 0 Canada
B 2014 1 0 Canada
B 2014 2 0 Canada
B 2014 3 0 Canada
B 2014 4 0 Canada
;
run;
data hash_lookup;
if _n_=0 then set have;
if _n_=1 then do;
declare hash h(dataset:"have(where=(location='USA'))");
h.definekey('person');
h.definedata('person');
h.definedone();
call missing(person);
end;
set have;
rc=h.find();
if rc eq 0;
drop rc;
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.