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

[J_Yetman]

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

[HB]

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

[PGStats · Accepted solution]

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;

[HB]

Aha.

So 

"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. 

[Ksharp]

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;

[PeterClemmensen]

@Ksharp Your Ifn Function is redundant 🙂

[andreas_lds]

An another one:

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