topic Re: How to use an IF THEN statement to apply to a group of records in SAS Programming

How to use an IF THEN statement to apply to a group of records

sashelp123 — Wed, 27 Nov 2019 20:09:20 GMT

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

Re: How to use an IF THEN statement to apply to a group of records

Shmuel — Wed, 27 Nov 2019 20:17:32 GMT

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

Re: How to use an IF THEN statement to apply to a group of records

Kurt_Bremser — Wed, 27 Nov 2019 20:18:25 GMT

data want;
merge
  have (
    in=h
    keep=person location
    where=(location = 'USA')
  )
  have
;
by person;
if h;
run;

Re: How to use an IF THEN statement to apply to a group of records

r_behata — Wed, 27 Nov 2019 20:52:56 GMT

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;