proc sql finds data where there is none

[ChrisNZ]

Hi,

The first query doesn’t find data:

• NOTE: No rows were selected. in the log
• &t1=

while the second one does, though there is no matching data in the table.

• No message in the log (when is proc sql going to mention observations processed? When?)
• &t2=999

Anyone can provide an explanation to this strange behaviour? Is it a defect?

%let t1= ; 
%let t2= ; 
proc sql ;
  select min(999,WEIGHT) into :t1 from SASHELP.CLASS where AGE=2;
quit;
proc sql ;
  select min(999,max(WEIGHT)) into :t2 from SASHELP.CLASS where AGE=2;
quit;
%put &=t1 &=t2;

[LaurieF]

Yup. max(weight) will always return missing (first principles). The min function picks that up after the query, and then returns the mininum value of 999 and missing (999, by definition). It's almost as if it's turning the rest into a subquery and acting on it.

It's something I quite often take advantage of when I don't want a macro variable to have a missing value:

proc sql noprint;
select coalesce(max(weight), 0)
  into :test_query
  from sashelp.class
 where age = 2;
quit;

%put &=test_query;

PS I like the debugging %put &=varname syntax. I never knew!

[ChrisNZ]

Thanks for the quick reply!

WEIGHT wil also return missing since there is no data, just like max(WEIGHT). Why the difference?

And mostly why does the statement even execute when no row is returned?

> It's something I quite often take advantage of when I don't want a macro variable to have a missing value

coalesce() does this for you, the dark magic of returning non-existing data is not needed for this, is it?

>I like the debugging %put &=varname syntax. I never knew!

Yes, very handy!  🙂

[LaurieF]

Oh I see what you mean. OK - I'll revise that. The implicit group by of a function like max (where its the function activing over the whole query) acts after the primary query (the from and where).

Your first query is using a SAS function min; your second query is using an SQL min. They function differently.

[PGStats]

min(999,WEIGHT) into :t1

is never evaluated because of the condition ( try it after %let t1=x; ), whereas

select max(WEIGHT) from SASHELP.CLASS where AGE=2

returns a missing value, then 

min(999, .) into :t2

puts 999 into t2.

[ChrisNZ]

@LaurieF Thank you. I am starting to get it.

@PGStats This almost makes sense! 🙂 Would you mind breaking the logic in a similar manner without the into clause?

The second select statement generates a report while the first one doesn't. I'd like to see where "select" appears in the broken-down steps?

[PGStats · Accepted solution]

Look at this progression:

proc sql;
select age from sashelp.class;
select age from sashelp.class where 0;
select count(*), min(age) from sashelp.class where 0;
select count(*), min(age) from sashelp.class where 0 having 0;
quit;

try to guess which queries return something.

[ChrisNZ]

Thank you both.

There's always something to learn isn't there?