PROC SQL WARNING 1-322: Assuming the symbol OR was misspelled as order

[agoldma]

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?

[ChrisHemedinger]

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;

[agoldma]

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]

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]

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).

[ballardw]

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.

[ClarkLawson]

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 · Accepted solution]

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]

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]

---

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

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)