PROC SQL using GROUP BY and ORDER BY together

[gabnash]

I have a piece of code that looks like this - without the right parenthesis after the GROUP BY clause, the ORDER BY clause generates a syntax error. Is this a fault? I couldn't find any mention of this in the SAS 9.3 manuals.

PROC SQL;

    Create Table z1_Cta_TY_v3_1 as            

(

  SELECT working_month,

   , company

   , room

   , route2

   , Destination

   , Origin

   , Work_area

   , A_Week_Ending

   , B_Week_Ending

   , SUM(Hours_TY)

   , SUM(Hourss_LY)

  FROM work.z1_Cta_TY_v2_1

  GROUP BY 1,2,3,4,5,6,7,8,9)

  ORDER BY 1,2,3,4,5,6,7,8,9 desc

;           

RUN;

[RW9]

Could you post the error.

[LinusH]

Well, you can't have parenthesis at those places - why do you have them?

Can't see why you should need them at all.

[RW9]

Sorry LinusH, it shouldn't matter about the parenthesis, that was the first thing I tried:

proc sql;

  create table tmp as

  (

  select  type,

          count(model) as tmp

  from    sashelp.cars

  group by type

  )

  order by type desc;

quit;

[Ksharp]

Order by will be executed at the last time. therefore you don't need ( ) at all.

PROC SQL;

    Create Table z1_Cta_TY_v3_1 as           

  SELECT working_month,

   , company

   , room

   , route2

   , Destination

   , Origin

   , Work_area

   , A_Week_Ending

   , B_Week_Ending

   , SUM(Hours_TY) as sum_ty

   , SUM(Hourss_LY) as sum_ly

  FROM work.z1_Cta_TY_v2_1

  GROUP BY 1,2,3,4,5,6,7,8,9

  ORDER BY 1,2,3,4,5,6,7,8,9 desc

;          

RUN;

Xia Keshan

[gabnash]

This was the actual code that got the error:

PROC SQL;

    Create Table z1_Cta_TY_v3_1 as            

(

  SELECT working_month,

   , company

   , room

   , route2

   , Destination

   , Origin

   , Work_area

   , A_Week_Ending

   , B_Week_Ending

   , SUM(Hours_TY)

   , SUM(Hourss_LY)

  FROM work.z1_Cta_TY_v2_1

  GROUP BY 1,2,3,4,5,6,7,8,9

  ORDER BY 1,2,3,4,5,6,7,8,9 desc

) ;           

RUN;

Note the closing paren is after the ORDER BY group. The error was as follows:

298          GROUP BY 1,2,3,4,5,6,7,8,9
299          ORDER BY 1,2,3,4,5,6,7,8,9 desc
             -----
             79
300      );
         -
         22
         200
ERROR 79-322: Expecting a ).

ERROR 22-322: Syntax error, expecting one of the following: ;, ','.

ERROR 200-322: The symbol is not recognized and will be ignored.

[RW9]

Yep, you can't use group by and order by in the same section as they counteract each other, i.e. group by will order the data.  Also note that you finish proc SQL with a quit; not a run;.   May as well drop the parenthesis as not needed.

[Reeza]

The code you initially posted will not generate an error. The brackets at the end of the order by will generate an error.

This is because you can't order a sub query, which is what that becomes.

Removing the brackets solves the issue.