CODEWITHSUNDEEP
sqlsas
Max Kim
Max Kim

Reputation: 1124

SQL select statement with and SAS

I was trying to write a SQL select statement with a nested and to obtain some data:

PROC SQL;

CREATE TABLE WORK.TEMP AS

SELECT DISTINCT

(CASE WHEN t1.TYPE_OF_SALE = 'Cake' AND t1.ADD_ON_FLAG = 0 THEN 'Arrangements'

WHEN t1.TYPE_OF_SALE = 'Cake' AND t1.ADD_ON_FLAG = 1 THEN 'Add-ons'

ELSE 'Direct' END) SALE_TYPE, #ERROR IN THIS LINE


(t1.UNIT_PRICE+t1.SERVICE_CHARGE_AMT) AS TOTAL_VAL

FROM WORK.FB_ORDERS t1

ORDER BY SALE_TYPE;

QUIT;

I for some reason get an error in my first line, is it not possible to use and with a case statement?

Error Message:
Error 22-322: Syntax error, expecting one of the following: !,!!,&...

This error exists right at the end of the case statement

Upvotes: 0

Views: 1171

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I believe the problem is that SAS requires as for defining a column alias:

PROC SQL;
CREATE TABLE WORK.TEMP AS
    SELECT DISTINCT (CASE WHEN t1.TYPE_OF_SALE = 'Cake' AND t1.ADD_ON_FLAG = 0 THEN 'Arrangements'
                          WHEN t1.TYPE_OF_SALE = 'Cake' AND t1.ADD_ON_FLAG = 1 THEN 'Add-ons'
                          ELSE 'Direct'
                      END) as SALE_TYPE,
                    (t1.UNIT_PRICE+t1.SERVICE_CHARGE_AMT) AS TOTAL_VAL
    FROM WORK.FB_ORDERS t1
    ORDER BY SALE_TYPE;

QUIT;

SAS allows other expressions after a column, such as FORMAT, so I think the as is needed to prevent ambiguity in the expressions.

Upvotes: 5

Joe
Joe

Reputation: 63424

You need an AS:

proc sql;
select distinct
(case when age<14 and sex='F' then "Young Girl"
      when age<14 and sex='M' then "Young Boy"
      else "Other" end) as type from sashelp.class;
      quit;

Upvotes: 2

Related Questions