DATA Step, Macro, Functions and more

proc sql finds data where there is none

Accepted Solution Solved
Reply
PROC Star
Posts: 1,759
Accepted Solution

proc sql finds data where there is none

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;

Accepted Solutions
Solution
‎01-17-2017 06:02 PM
Respected Advisor
Posts: 4,920

Re: proc sql finds data where there is none

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.

PG

View solution in original post


All Replies
Super Contributor
Posts: 252

Re: proc sql finds data where there is none

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!

PROC Star
Posts: 1,759

Re: proc sql finds data where there is none

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!  Smiley Happy

Super Contributor
Posts: 252

Re: proc sql finds data where there is none

[ Edited ]

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.

Respected Advisor
Posts: 4,920

Re: proc sql finds data where there is none

[ Edited ]

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.

 

 

PG
PROC Star
Posts: 1,759

Re: proc sql finds data where there is none

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

@PGStats This almost makes sense! Smiley Happy 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?

Solution
‎01-17-2017 06:02 PM
Respected Advisor
Posts: 4,920

Re: proc sql finds data where there is none

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.

PG
PROC Star
Posts: 1,759

Re: proc sql finds data where there is none

Thank you both.

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

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 187 views
  • 4 likes
  • 3 in conversation