03-24-2014 05:04 AM
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
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
03-24-2014 05:33 AM
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...?
03-24-2014 06:10 AM
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 .
03-24-2014 07:32 AM
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.
03-24-2014 12:25 PM
Yes You are right
Here is my input table 1
And here is my input table 2
So after execution my table 1 should look like this
Need help in solving this. Can subquery work over here ?
03-24-2014 12:55 PM
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.
03-24-2014 06:21 AM
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??
03-24-2014 01:33 PM
Hi this will give you the desired output as stated in above comment:
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,
%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
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