topic Re: proc sql finds data where there is none in SAS Programming

proc sql finds data where there is none

ChrisNZ — Mon, 16 Jan 2017 22:18:45 GMT

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;

Re: proc sql finds data where there is none

LaurieF — Mon, 16 Jan 2017 22:27:36 GMT

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!

Re: proc sql finds data where there is none

ChrisNZ — Mon, 16 Jan 2017 22:41:21 GMT

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! 🙂

Re: proc sql finds data where there is none

LaurieF — Mon, 16 Jan 2017 22:50:29 GMT

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.

Re: proc sql finds data where there is none

PGStats — Mon, 16 Jan 2017 23:00:04 GMT

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.

Re: proc sql finds data where there is none

ChrisNZ — Tue, 17 Jan 2017 00:24:58 GMT

@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?

Re: proc sql finds data where there is none

PGStats — Tue, 17 Jan 2017 04:58:57 GMT

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.

Re: proc sql finds data where there is none

ChrisNZ — Wed, 18 Jan 2017 00:51:44 GMT

Thank you both.

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