Hi,
The first query doesn’t find data:
while the second one does, though there is no matching data in the table.
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;
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.
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!
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! 🙂
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.
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.
@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?
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.
Thank you both.
There's always something to learn isn't there?
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: