topic Re: Case when used with a subquery strange result.. in SAS Procedures

Case when used with a subquery strange result..

saskapa — Thu, 26 Mar 2015 11:57:56 GMT

Hi,

Can someone tell me why does this code return a flag '1' while it shouldn't ?

proc sql;

create table test as

select region,case

when region in (select region from sashelp.bmt ) then '1'

else '0'

end

as case

from sashelp.demographics;

quit;

Obviously, Region variable is not in BMT dataset so Case variable should be set to 0.

Thanks in advance,

Skp

Re: Case when used with a subquery strange result..

PGStats — Thu, 26 Mar 2015 17:56:19 GMT

Puzzling indeed, at first! Here is a hint, try this:

proc sql;

create table test as

select

d.region,

case

when d.region in (select d.region from sashelp.bmt) then '1'

else '0'

end as case

from sashelp.demographics as d;

quit;

Explanation: The reference to column region in the subquery is assumed to be from table sashelp.demographics because region is not a column in sashelp.bmt.

Re: Case when used with a subquery strange result..

Reeza — Thu, 26 Mar 2015 18:57:23 GMT

I agree with @pgstats and think this is considered an inline query not a subquery - IIRC, inline queries run for each observation whereas a subquery would run only once.

Re: Case when used with a subquery strange result..

PGStats — Thu, 26 Mar 2015 19:46:21 GMT

I couldn't find any reliable source to clarify this point. The expression "inline query" is never mentioned in the 600+ pages standard manual for SQL92. Subqueries however are divided into scalar-, row- and table- subqueries. Thus it seems that any query within another query is a subquery, as far as the standard goes.

Re: Case when used with a subquery strange result..

Reeza — Thu, 26 Mar 2015 19:50:46 GMT

Then I probably don't recall correctly

I'm thinking of when I used to use T-SQL and those types of queries were used occasionally but it's been a *few* years now!

Re: Case when used with a subquery strange result..

gergely_batho — Fri, 27 Mar 2015 15:35:27 GMT

It is called correlated sub-query.

But usually it really correlates to the outer query.

Re: Case when used with a subquery strange result..

saskapa — Mon, 30 Mar 2015 08:46:49 GMT

Thanks PGStats ! About the terminology, I couldn't find any 'Inline querry' terminology...I looks like that it is indeed a correlated sub-query.

Cheers

skp

Re: Case when used with a subquery strange result..

jwillis — Mon, 30 Mar 2015 19:06:39 GMT

I am using 9.4 64 bit and I still receive a '1' for case. Can you give a stronger hint?

proc sql;

drop table test;

create table test as

select

d.region,

case

when d.region in (select d.region from sashelp.bmt ) then '1'

else '0'

end as case

from sashelp.demographics as d ;

quit;

Obs region case

1 AMR 1

2 AMR 1

3 AMR 1

4 AMR 1

5 AMR 1

6 AMR 1

7 AMR 1

Re: Case when used with a subquery strange result..

Reeza — Mon, 30 Mar 2015 19:12:20 GMT

The subquery or whatever it's called assumes that region in the select statement is from sashelp.demographics because there is no alias prefix to tell it otherwise so it defaults to whatever table it finds the variable in.

The following will generate the results you expect:

proc sql;

drop table test;

create table test as

select

D.region,

case

when d.region in (select B.region from sashelp.bmt as B ) then '1'

else '0'

end as case

from sashelp.demographics as D ;

quit;

Re: Case when used with a subquery strange result..

PGStats — Mon, 30 Mar 2015 19:36:43 GMT

Notice the "d." prefix to column name region in the subquery. d.region refers to column region in sashelp.demographics. As far as the subquery is concerned, d.region is a constant (just like as if you said select "A" from sashelp.bmt, try it! ), the result of which being the value of the constant repeated as many times as there are lines in sashelp.bmt. Note that column region doesn't even exists in table sashelp.bmt, which is why region in the original question was considered to mean d.region by the SQL compiler. Thus, the when condition is always true because b.region is always part of a column of repeated b.region values.