BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
saskapa
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

9 REPLIES 9
PGStats
Opal | Level 21

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
jwillis
Quartz | Level 8

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

Reeza
Super User

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;

PGStats
Opal | Level 21

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
Reeza
Super User

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.

PGStats
Opal | Level 21

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
Reeza
Super User

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!

gergely_batho
SAS Employee

It is called correlated sub-query.

But usually it really correlates to the outer query.

saskapa
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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