PIVOT function in proc sql statement in SAS 9.4 not working

Question

below is the code and error I get, can anyone help?

     proc sql;
    SELECT DateOfFill, DUEXIS AS DUEXIS, PENNSAID AS PENNSAID, RAYOS AS RAYOS,VIMOVO AS VIMOVO
     FROM (select DateOfFill, product, ftrx from PSKW.PSKWMaster) src
    pivot
    (
    sum(ftrx) for [Product] in 
    (DUEXIS, PENNSAID,RAYOS,VIMOVO)) PVT;
    quit;



ERROR 76-322: Syntax error, statement will be ignored.

5    (
6    sum(ftrx) for [Product] in
7    ([DUEXIS], [PENNSAID],[RAYOS],[VIMOVO])) PVT;
8    quit;

NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.03 seconds cpu time 0.01 seconds

9 SELECT DateOfFill, DUEXIS AS DUEXIS, PENNSAID AS PENNSAID, RAYOS AS RAYOS,VIMOVO AS VIMOVO ------ 180 ERROR 180-322: Statement is not valid or it is used out of proper order.

10    FROM (select DateOfFill, product, ftrx from PSKW.PSKWMaster) src
11   pivot
12   (
13   sum(ftrx) for [Product] in
14   (DUEXIS, PENNSAID,RAYOS,VIMOVO)) PVT;

15   quit;

there seems to be a close brace missing for the opening brace after pivot — Utsav
Hint. Count number of ( then count number of ). Do they match? — Hogan
If this is SAS, PROC SQL does not support PIVOT command, except in direct pass through to the server. — Reeza

Tom Tom · Accepted Answer · 2017-01-10T15:31:24

That does not look like valid syntax for PROC SQL. Perhaps you meant to use a passthru query into your SQL database?

proc sql;
  connect to odbc (.....);
  select * from connection to odbc
     (select ... pivot ... )
  ;
quit;

PIVOT function in proc sql statement in SAS 9.4 not working

1 Answers