DATA Step, Macro, Functions and more

If then statement to case statement

Reply
Frequent Contributor
Posts: 85

If then statement to case statement

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

Super User
Posts: 5,260

Re: If then statement to case statement

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


Data never sleeps
Frequent Contributor
Posts: 85

Re: If then statement to case statement

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 .

Super User
Posts: 5,260

Re: If then statement to case statement

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.

Data never sleeps
Frequent Contributor
Posts: 85

Re: If then statement to case statement

Yes You are right

Here is my input table 1

num_var1num_var2num_var3num_var4num_var5num_var6charvar1charvar2charvar3charvar4charvar5charvar6
125736anpbac
236412djodbx
414353cbzscb
552441edytdy
641225fmxrra
763144klaejz

And here is my input table 2

char_var
a
b
c
d

So after execution my table 1 should look like this

num_var1num_var2num_var3num_var4num_var5num_var6charvar1charvar2charvar3charvar4charvar5charvar6Flag1Flag2Flag3Flag4Flag5Flag6
125736anpbac110111
236412djodbx111111
414353cbzscb110111
552441edytdy011011
641225fmxrra101111
763144klaejz111100

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

Super Contributor
Posts: 339

Re: If then statement to case statement

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

Frequent Contributor
Posts: 106

Re: If then statement to case statement

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??

Frequent Contributor
Posts: 106

Re: If then statement to case statement

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;

Ask a Question
Discussion stats
  • 7 replies
  • 279 views
  • 0 likes
  • 4 in conversation