topic Re: How to set a variable in a table based on multiple records in another table in SAS Programming

How to set a variable in a table based on multiple records in another table

J_Yetman — Tue, 10 May 2022 15:25:18 GMT

This is what I am trying to do -

Data_set_1:

PID

012345

012356
014589

Data_set_2:

PID Type

012345 A

012345 B

012356 A

014589 B

I'm looking to create a table, using the PID as the unique value between the datasets, which returns the PID from Data_set_1, and a variable field that returns a 1 value when ANY record in Data_Set_2 for that PID as a Type value of B.

So I want my final data set to look like:

PID Var

012345 1

012356 0

014589 1

Re: How to set a variable in a table based on multiple records in another table

HB — Tue, 10 May 2022 16:57:36 GMT

Here is one way. There are better ways.

With data like

data set1;
   input pid;
datalines;
012345
012356
014589
;
run;

data set2;
   input pid type $1.;
datalines;
012345 A
012345 B
012356 A
014589 B
;
run;

you can do

data midstep;
	merge set1 (in = a) set2;
	by pid;
	if a and type = 'B' then myvar = 1;
		else myvar = 0;
run;

proc sql;
	select pid, max(myvar)
	from midstep
	group by pid;
quit;

to get

pid
12345	1
12356	0
14589	1

Re: How to set a variable in a table based on multiple records in another table

PGStats — Tue, 10 May 2022 19:22:46 GMT

Can't think of anything simpler than:

proc sql;
select 
    pid,
    "B" in (select type from set2 where pid = set1.pid) as var
from set1;
quit;

Re: How to set a variable in a table based on multiple records in another table

HB — Tue, 10 May 2022 21:59:15 GMT

Aha.

"B" in (select type from set2 where pid = set1.pid)

is returning a TRUE (1) if it sees a "B" in the list of returned set2 types for each set1 PID, otherwise it is returning a FALSE (0). Would not have ever come up with that.

Re: How to set a variable in a table based on multiple records in another table

Ksharp — Wed, 11 May 2022 03:58:42 GMT

data set1;
   input pid;
datalines;
012345
012356
014589
;
run;

data set2;
   input pid type $1.;
datalines;
012345 A
012345 B
012356 A
014589 B
;
run;

data want;
 if _n_=1 then do;
  declare hash h(dataset:'set2(where=(type="B"))');
  h.definekey('pid');
  h.definedone();
 end;
set set1;
var=ifn(h.check()=0,1,0);
run;

Re: How to set a variable in a table based on multiple records in another table

andreas_lds — Wed, 11 May 2022 05:35:45 GMT

An another one:

data want;
   merge
      set1
      set2(where=(type = 'B'))
   ;
   by pid;
   
   Var = not missing(type);
   
   drop type;
run;

Re: How to set a variable in a table based on multiple records in another table

PeterClemmensen — Wed, 11 May 2022 09:12:57 GMT

@Ksharp Your Ifn Function is redundant 🙂