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

 

This query works well

proc sql outobs=100;

   select   distinct name 

   from     sashelp.class 

   where    age>10 and Sex='M' 

   order by name

;quit;

 

This query produces a warning in SAS 9.4 (M3 and M4)

proc sql outobs=100;

   select name

   from  (select   distinct name 

          from     sashelp.class 

          where    age>10 and Sex='M' 

          order by name              )

;quit;

I agree that this query is not very useful, but I want to do something similar but a little more complicated, which requires a selection from a sub-query which has an ORDER BY clause, and the ORDER BY clause gets misinterpreted in the same way as in the example above.

Is it possible to tell SAS not guess that OR was misspelled as order?

This reminds me of Chris' blog:  Do me a favor: stop doing me favors 

Is there a system option to reduce or stop guessing?

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

You cannot sort the inner queries.  The ORDER BY clause only applies to the overall result.

If you want to sort by two variables the select two variables.

proc sql ;
select 1 as group,name
from sashelp.class
where sex='M'

union

select 2 as group,name
from sashelp.class
where sex='F'

order by group, name 
;

View solution in original post

10 REPLIES 10
ChrisHemedinger
Community Manager

I think SAS is not expecting/allowing the ORDER BY within the SELECT clause.  Since you want to order the results of the final query, it should go outside.

 

proc sql outobs=100;
   select name
   from  (select   distinct name 
          from     sashelp.class 
          where    age>10 and Sex='M' 
          )
   order by name 
;quit;
It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
agoldma
Pyrite | Level 9

This is the more complicated query that I really want to do:

 

proc sql outobs=100;
   select name
   into  :var_list separated by ','
   from  (select   distinct name  
          from     sashelp.class  
          where    age>10 and Sex='M'  
          order by upper(name) 
         UNION ALL CORR
          select   distinct name  
          from     sashelp.class  
          where    age>10 and Sex='F'  
          order by upper(name)
          )
;quit;

 

I would like the sorting done prior to the union, and I want to ignore case when sorting

This could be done in 3 steps, but I was hoping to do it in one

 

proc sql outobs=100;
   create view males as
   select   distinct name  
   from     sashelp.class  
   where    age>10 and Sex='M'  
   order by upper(name)
   ;
   create view females as
   select   distinct name  
   from     sashelp.class  
   where    age>10 and Sex='F'  
   order by upper(name)
   ;
   select  name
   into   :var_list separated by ','
   from   (select * from males 
           union all corr
           select * from females )
   ;
   drop view males, females
;quit;

 

agoldma
Pyrite | Level 9

My previous example didn't work well because apparently the views don't preserve the sort order if sorting is done by variables that are not in the SELECT statement. This works better (relying on a free sort provided by DISTINCT)

proc sql outobs=100;
   create view males as
   select   distinct upper(name), name  
   from     sashelp.class  
   where    age>10 and Sex='M'  
   ;
   create view females as
   select   distinct upper(name), name  
   from     sashelp.class  
   where    age>10 and Sex='F'  
   ;
   select  name
   into   :var_list separated by ','
   from   (select name from males 
           union all corr
           select name from females )
   ;
   drop view males, females
;quit;
ChrisHemedinger
Community Manager

I think that sort operations within SQL are intentionally opaque.  PROC SQL -- or the database process for passthrough queries -- will decide how to sort based on what is needed.  Your final ORDER BY says how you want the results in sequence after the result set is determined.

 

But yes -- "free" sorting with DISTINCT -- that's a PROC SQL behavior you can leverage (but doesn't necessarily apply to database queries).

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
ballardw
Super User

How about:

 

proc sql noprint;
   select name into : varlist separated by ','
   from sashelp.class
   where age>10
   order by sex descending,upper(name)
   ;
quit;

 

I assume this is actually a demonstration of a principle you are attempting to use but perhaps you are over complicating it.

Of course if your actual data has duplicates of the "name" variable the select may want to be select distinct name

 

I am also a tad leery of macro variables with commas. It complicates things if you want to pass the variable as a parameter to another macro.

Clark
Obsidian | Level 7

I'm not sure your desired outcome as you don't provide content.

 

Not having that, I would question the need to create the two views prior to creating a union. Couldn't you just sort by Name, Sex? As it looks like you want a list of males then females?

Tom
Super User Tom
Super User

You cannot sort the inner queries.  The ORDER BY clause only applies to the overall result.

If you want to sort by two variables the select two variables.

proc sql ;
select 1 as group,name
from sashelp.class
where sex='M'

union

select 2 as group,name
from sashelp.class
where sex='F'

order by group, name 
;
agoldma
Pyrite | Level 9

Clark and ballrdw, you're right that the "sashelp.class" is too simple of an example.

In my data I have duplicate records and "name" is spelled in very-mixed case.

I really want to take distinct and sort by upper(name)

Thank you for suggesting to avoid the union... and to sort instead

Instead of stacking males on top of females, I can sort... but only if it's a hidden sort inside DISTINCT

I wish I could do this:

proc sql outobs=100;
   select name
   into  :var_list separated by ','
   from  (select   distinct Sex, upper(name), name  
          from     sashelp.class  
          where    age>10
          order by Sex desc, upper(name)
          )
;quit;

Unfortunately, SAS doesn't understand the ORDER BY clause.

Fortunately, I can rely on the free sort provided by DISTINCT, and I can avoid the union, so I'll think I'll go with this:

proc sql outobs=100;
   select name
   into  :var_list separated by ','
   from  (select   distinct ifn(Sex='M',0,1), upper(name), name  
          from     sashelp.class  
          where    age>10
          )
;quit;

I accepted Tom's answer as the solution because it sounds like a theoretical thing that I need to remember for the future and because it answers my original question about why ORDER BY doesn't work.

Tom
Super User Tom
Super User

@agoldma wrote:

Clark and ballrdw, you're right that the "sashelp.class" is too simple of an example.

In my data I have duplicate records and "name" is spelled in very-mixed case.

I really want to take distinct and sort by upper(name)

Thank you for suggesting to avoid the union... and to sort instead

Instead of stacking males on top of females, I can sort... but only if it's a hidden sort inside DISTINCT

I wish I could do this:

proc sql outobs=100;
   select name
   into  :var_list separated by ','
   from  (select   distinct Sex, upper(name), name  
          from     sashelp.class  
          where    age>10
          order by Sex desc, upper(name)
          )
;quit;

Unfortunately, SAS doesn't understand the ORDER BY clause.

Fortunately, I can rely on the free sort provided by DISTINCT, and I can avoid the union, so I'll think I'll go with this:

proc sql outobs=100;
   select name
   into  :var_list separated by ','
   from  (select   distinct ifn(Sex='M',0,1), upper(name), name  
          from     sashelp.class  
          where    age>10
          )
;quit;

I accepted Tom's answer as the solution because it sounds like a theoretical thing that I need to remember for the future and because it answers my original question about why ORDER BY doesn't work.


I don't get it. What is the difference between this working code

select name
  into  :var_list separated by ','
from  sashelp.class 
where age>10
order by Sex desc, upper(name)
;

And your proposed solution with the attempt to embed and ORDER BY into the middle somewhere?

If you want to suppress the warnings about order by variables not selected the select them.

select sex,upper(name),name
  into  :dummy,:dummy,:var_list separated by ','
from  sashelp.class 
where age>10
order by Sex desc, upper(name)
;
agoldma
Pyrite | Level 9

Tom, you're right

This also works

I just didn't think of assigning those other variables into a dummy macro variable

Thank you

(I would also add the  %symdel dummy;  at the end)

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 10 replies
  • 4664 views
  • 6 likes
  • 5 in conversation