Using multiple subqueries with CASE/WHEN

[Chrisas]

Hello everyone!

I would like to update the values of a variable using 3 conditions. Particularly, I want to add a third value in the variable named "Flag" when the values of the variables (var1, var2, var3) are the same with the corresponding ones of the table B at the same time.

Unfortunately, the below code does not work properly. 

proc sql;

update tableA

set flag = case when (var1 in (select var1 from tableB) and var2 in (select var2 from tableB) and var3 in (select var3 from tableB))

then '3'

end;

quit;

What I want to achieve (an example): 

Table A:

var1	var2	var3	flag
1	April	12	1
2	May	14	2
3	June	15	2
4	July	17	3

Table B:

var1	var2	var3
1	April	13
4	July	17

Do you have any idea?

Thank you in advance!

[Kurt_Bremser]

I would do such in a data step:

data tablea_new;
set tablea;
if _n_ = 1
then do;
  declare hash v1 (dataset:"tableb");
  v1.definekey("var1");
  v1.definedone();
  declare hash v2 (dataset:"tableb");
  v2.definekey("var2");
  v2.definedone();
  declare hash v3 (dataset:"tableb");
  v3.definekey("var3");
  v3.definedone();
end;
if v1.check() = 0 and v2.check() = 0 and v3.check() = 0 then flag = "3";
run;

[Chrisas]

Thank you very much for your response!

But, again I have the same issue. For example, it flags a case equal to 3, while the three conditions are not satisfied at the same time. How is this possible?

[Kurt_Bremser]

If you only want those where all conditions are met within a single observation of the second table, then you must use a join instead of subqueries.

[Tom]

That is a very strange looking query. You are testing to see if all of the values of VAR1,VAR2, and VAR3 were each ever independently includes in those similarly named variables in TABLEB.  Wouldn't it be clearer to keep the set of values you want to test against in three different datasets? 

Can you show some example data for TABLEA and TABLEB?

Make sure to include some where you want the flag to be changed to '3' and some where you don't.

[Chrisas]

Hi, thank you very much for your response! I have edited my post and I added an example.

[Ksharp]

As Tom said . Post an example include input Dataset and output Dataset ,that could explain your question very well.

Or maybe you want this one .

proc sql;

update tableA as a

set flag = case when exists(   select * from tableB as b where catx('|',b.var1, b.var2 , b.var3) = catx('|',a.var1, a.var2 , a.var3)   )
then '3'
end;

quit;

[Tom]

Updating an existing dataset is not a normal process flow for data analysis.

Sounds like you just want to merge the datasets by the three key variables and set FLAG to 3 when there is a match.

So let's make some actual datasets from your posted listings.

data A ;
  input var1 var2 $ var3 expect;
cards;
1 April 12 1
2 May   14 2
3 June  15 2
4 July  17 3
;

data B;
  input var1 var2 $ var3;
cards;
1 April 13
4 July  17
;

Now a simple merge will let us test if the records in A are also in B or not.

data want ;
  merge A(in=ina) B(in=inb);
  by var1-var3;
  if ina;
  if inb then flag=3;
run;

Results

Obs    var1    var2     var3    expect    flag

 1       1     April     12        1        .
 2       2     May       14        2        .
 3       3     June      15        2        .
 4       4     July      17        3        3

It is not clear to me where the values of 1 and 2 that were in your original table A are supposed to come from.

[PGStats · Accepted solution]

Use an EXISTS clause in your UPDATE query:

data A ;
  input var1 var2 $ var3 expect;
cards;
1 April 12 1
2 May   14 2
3 June  15 2
4 July  17 999
;

data B;
  input var1 var2 $ var3;
cards;
1 April 13
4 July  17
;

proc sql;
update A 
set expect = 3 
where exists (select * from B where var1 = A.var1 and var2 = a.var2 and var3=A.var3);
select * from A;
quit;