topic Re: Problem when using order by in SAS Programming

Problem when using order by

Eileen1496 — Sun, 06 Aug 2023 20:58:57 GMT

Hi everyone,

I'm new to SAS. I was trying the order by function using a dataset from sashelp library (sashelp.cars). I want to sort by msrp in ascending order. However, as I run my code, it does not sort msrp in ascending order. I could not find any mistakes in my code.

Can you help me find out what happens?

Thank you!

Code:

proc sql;
select cars.make, cars.model, cars.msrp as tax
from sashelp.cars
where msrp<=40000;
order by msrp;
quit;

Re: Problem when using order by

FreelanceReinh — Sun, 06 Aug 2023 21:14:41 GMT

Hi @Eileen1496 and welcome to the SAS Support Communities!

Remove the semicolon after the WHERE clause. Otherwise the ORDER BY clause is not part of the query and causes a syntax error (did you see the message in the log?).

Re: Problem when using order by

ballardw — Sun, 06 Aug 2023 21:18:34 GMT

It is best to show the LOG when discussing code.

In the case of what you posted it clearly shows an error.

 proc sql;
8    select cars.make, cars.model, cars.msrp as tax
9    from sashelp.cars
10   where msrp<=40000;
NOTE: Writing HTML Body file: sashtml.htm
11   order by msrp;
     -----
     180
ERROR 180-322: Statement is not valid or it is used out of proper order.

12   quit;

The semicolon at the end of the Where clause ends the SELECT. So the Order By starts a new statement and is not legal as the first clause of a query.

Remove the semicolon after the Where msrp<=40000

@Eileen1496 wrote:

Hi everyone,

I'm new to SAS. I was trying the order by function using a dataset from sashelp library (sashelp.cars). I want to sort by msrp in ascending order. However, as I run my code, it does not sort msrp in ascending order. I could not find any mistakes in my code.

Can you help me find out what happens?

Thank you!

Code:
proc sql;
select cars.make, cars.model, cars.msrp as tax
from sashelp.cars
where msrp<=40000;
order by msrp;
quit;

Re: Problem when using order by

Eileen1496 — Sun, 06 Aug 2023 21:41:24 GMT

Thank you! You are right it is this problem.

This time since it still gave me output, so I did not think there would be a problem and did not check the log. In the log, there is indeed this error. Next time I would check log anyway!

Re: Problem when using order by

Eileen1496 — Sun, 06 Aug 2023 21:42:01 GMT

Thank you, it indeed solves the problem!