If then statement to case statement

[forumsguy]

Hi All,

I have two tables. My table 1 consists of following  numeric variables

num_var1, num_var2, num_var3, num_var4, num_var5, num_var6

  and following  character variables

charvar1,charvar2,charvar3,charvar4,charvar5,charvar6

Table 2 has only one variable char_var

and I have following code which I need to convert to Proc SQL code since I have to use it in SAS DI Studio.

if table1.num_varn not in (4,5) or table2.charvarn in

(table2.char_var) then Table1.flagn = 0;else table1.flagn = 1;

Note :  In above code n has values from 1 to 6

But somehow I am unable to crack the correct logic. Any help is really appreciated. Many thanks in advance

[LinusH]

Do you know about case syntax?

You example is pretty straight forward, replace if with case when, replace assignment statements with just the result, remove all ; and end it with a end as flagn.

Allthough, I'm not sure how you expect the part "table2.charvarn in (table2.char_var)" should work...?

[forumsguy]

Hi Linus,

I know about case. And  I am stuck exactly at place what you have mentioned.

part "table2.charvarn in (table2.char_var)"

My question is there is no joining condition mentioned so how can we get values from other table without joining condition in proc sql . All i want is just get char value from table1 and if its present in table2 then create new variable flag1 in table .

[LinusH]

By just showing the logic for this assignment, it's hard to tell how the whole query should look like.

A sub-query could (perhaps) solve you problem, depending on what you want to achieve, but still, both columns are in table2, or is it a typo?

Perhaps it's time for you to present some sample input and desired output.

[forumsguy]

Yes You are right

Here is my input table 1

num_var1	num_var2	num_var3	num_var4	num_var5	num_var6	charvar1	charvar2	charvar3	charvar4	charvar5	charvar6
1	2	5	7	3	6	a	n	p	b	a	c
2	3	6	4	1	2	d	j	o	d	b	x
4	1	4	3	5	3	c	b	z	s	c	b
5	5	2	4	4	1	e	d	y	t	d	y
6	4	1	2	2	5	f	m	x	r	r	a
7	6	3	1	4	4	k	l	a	e	j	z

And here is my input table 2

char_var

a

b

c

d

So after execution my table 1 should look like this

num_var1	num_var2	num_var3	num_var4	num_var5	num_var6	charvar1	charvar2	charvar3	charvar4	charvar5	charvar6	Flag1	Flag2	Flag3	Flag4	Flag5	Flag6
1	2	5	7	3	6	a	n	p	b	a	c	1	1	0	1	1	1
2	3	6	4	1	2	d	j	o	d	b	x	1	1	1	1	1	1
4	1	4	3	5	3	c	b	z	s	c	b	1	1	0	1	1	1
5	5	2	4	4	1	e	d	y	t	d	y	0	1	1	0	1	1
6	4	1	2	2	5	f	m	x	r	r	a	1	0	1	1	1	1
7	6	3	1	4	4	k	l	a	e	j	z	1	1	1	1	0	0

Need help in solving this. Can subquery work over here ?

[Vince28_Statcan]

There are some inconsistencies with your output table versus the logic. You might want to review both. Examples of it would be row1 flag1 num_var1 is not in (4,5) which makes the condition if table1.num_varn not in (4,5) or table2.charvarn in  (table2.char_var) as true and thus the flag should be 0, not 1. My first guess was that you should've had an AND instead of an OR between the two conditions. However, this was also rejected by row2/flag4 because this would evaluate to true even if the OR had been replaced by an AND.

Anyway, I can still provide conceptual grounds for you to work with. The concept of "... in table2.char_var" can be achieved through the use of subquerries as pointed out above. The SQL syntax looks like

table1.charvarn in (select char_var from table2)

This turns the subquerry in parenthesis as a list of values for the in operator. That is, it's as though the subquerry in the parenthesis resolves to ('a', 'b', 'c', 'd') from the example data above.

Vincent

[pradeepalankar]

Hi,

shouldn't it be "table1.charvarn in (table2.char_var)" in place of "table2.charvarn in (table2.char_var)"

as charvar1-charvar6 are present in table1 not in table2 as per your comments in post??

[pradeepalankar]

Hi this will give you the desired output as stated in above comment:

%macro test;

proc sql;

create table test as select *,

%do i=1 %to 6;

%if &i<6 %then %do;

case when table1.num_var&i not in (4,5) or table1.charvar&i in (select table2.char_var from table2) then 1 else 0 end as flag&i,

%end;

%if &i=6 %then %do;

case when table1.num_var&i not in (4,5) or table1.charvar&i in (select table2.char_var from table2) then 1 else 0 end as flag&i

%end; %end;

from table1;

quit;

%mend;

%test;

approach 2: static code:

proc sql ;

create table test as select *,

case when table1.num_var1 not in (4,5) or table1.charvar1 in (select table2.char_var from table2) then 1 else 0 end as flag1,

case when table1.num_var2 not in (4,5) or table1.charvar2 in (select table2.char_var from table2) then 1 else 0 end as flag2,

case when table1.num_var3 not in (4,5) or table1.charvar3 in (select table2.char_var from table2) then 1 else 0 end as flag3,

case when table1.num_var4 not in (4,5) or table1.charvar4 in (select table2.char_var from table2) then 1 else 0 end as flag4,

case when table1.num_var5 not in (4,5) or table1.charvar5 in (select table2.char_var from table2) then 1 else 0 end as flag5,

case when table1.num_var6 not in (4,5) or table1.charvar6 in (select table2.char_var from table2) then 1 else 0 end as flag6

from table1;

quit;