BookmarkSubscribeRSS Feed
sashelp123
Calcite | Level 5

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 yearfiscal period# of vacationslocation
A201310Canada
A201320USA
A201330Canada
A201340Canada
A201410Canada
A201420Canada
A201431Canada
A201440Canada
B201310Canada
B201320Canada
B201330Canada
B201340Canada
B201410Canada
B201420Canada
B201430Canada
B201440Canada
3 REPLIES 3
Shmuel
Garnet | Level 18

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

r_behata
Barite | Level 11
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;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 674 views
  • 3 likes
  • 4 in conversation