Help using Base SAS procedures

PROC SQL using GROUP BY and ORDER BY together

Reply
New Contributor
Posts: 2

PROC SQL using GROUP BY and ORDER BY together

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;

Super User
Super User
Posts: 7,955

Re: PROC SQL using GROUP BY and ORDER BY together

Could you post the error.

Super User
Posts: 5,429

Re: PROC SQL using GROUP BY and ORDER BY together

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

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

Data never sleeps
Super User
Super User
Posts: 7,955

Re: PROC SQL using GROUP BY and ORDER BY together

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;

Super User
Posts: 10,028

Re: PROC SQL using GROUP BY and ORDER BY together

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

New Contributor
Posts: 2

Re: PROC SQL using GROUP BY and ORDER BY together

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.

Super User
Super User
Posts: 7,955

Re: PROC SQL using GROUP BY and ORDER BY together

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.

Super User
Posts: 19,810

Re: PROC SQL using GROUP BY and ORDER BY together

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.

Ask a Question
Discussion stats
  • 7 replies
  • 5614 views
  • 0 likes
  • 5 in conversation