BookmarkSubscribeRSS Feed
gabnash
Calcite | Level 5

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;

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Could you post the error.

LinusH
Tourmaline | Level 20

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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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.

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
  • 7 replies
  • 31207 views
  • 0 likes
  • 5 in conversation