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

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;
1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

7 REPLIES 7
LaurieF
Barite | Level 11

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
Tourmaline | Level 20

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
Barite | Level 11

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
Opal | Level 21

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
ChrisNZ
Tourmaline | Level 20

@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
Opal | Level 21

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
ChrisNZ
Tourmaline | Level 20

Thank you both.

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

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!

How to Concatenate Values

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.

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
  • 7 replies
  • 826 views
  • 4 likes
  • 3 in conversation