Help using Base SAS procedures

Case when used with a subquery strange result..

Accepted Solution Solved
Reply
Contributor
Posts: 54
Accepted Solution

Case when used with a subquery strange result..

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


Accepted Solutions
Solution
‎03-26-2015 01:56 PM
Respected Advisor
Posts: 4,640

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

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.

PG

PG

View solution in original post


All Replies
Solution
‎03-26-2015 01:56 PM
Respected Advisor
Posts: 4,640

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

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.

PG

PG
Regular Contributor
Posts: 217

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

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

Super User
Posts: 17,743

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

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;

Respected Advisor
Posts: 4,640

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

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.

PG  

PG
Super User
Posts: 17,743

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

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.

Respected Advisor
Posts: 4,640

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

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.

PG

PG
Super User
Posts: 17,743

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

Then I probably don't recall correctly Smiley Happy

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!

SAS Employee
Posts: 340

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

It is called correlated sub-query.

But usually it really correlates to the outer query.

Contributor
Posts: 54

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 511 views
  • 1 like
  • 5 in conversation