topic Re: PROC SQL using GROUP BY and ORDER BY together in SAS Procedures

PROC SQL using GROUP BY and ORDER BY together

gabnash — Wed, 14 May 2014 09:50:11 GMT

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;

Re: PROC SQL using GROUP BY and ORDER BY together

RW9 — Wed, 14 May 2014 10:03:44 GMT

Could you post the error.

Re: PROC SQL using GROUP BY and ORDER BY together

LinusH — Wed, 14 May 2014 10:38:25 GMT

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

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

Re: PROC SQL using GROUP BY and ORDER BY together

RW9 — Wed, 14 May 2014 10:40:12 GMT

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;

Re: PROC SQL using GROUP BY and ORDER BY together

Ksharp — Wed, 14 May 2014 11:58:13 GMT

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

Re: PROC SQL using GROUP BY and ORDER BY together

gabnash — Wed, 14 May 2014 12:15:17 GMT

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.

Re: PROC SQL using GROUP BY and ORDER BY together

RW9 — Wed, 14 May 2014 12:20:49 GMT

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.

Re: PROC SQL using GROUP BY and ORDER BY together

Reeza — Wed, 14 May 2014 14:19:49 GMT

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.