PROC SQL errors although same code works fine in SQL Server Management Studio

[jbrau123]

Dear community

I am having trouble using PROC SQL in SAS. All my SAS code runs fine, but any type of SQL makes errors. The structure below is supposed to work according to this support article: https://support.sas.com/resources/papers/proceedings/proceedings/sugi29/268-29.pdf

PROC SQL;

[my sql statement]

QUIT;

RUN;

I tried writing simple statements such as DROP TABLE IF EXISTS DATA.DATA or creating a table by 

CREATE TABLE [#A]
([Number] [bigint] IDENTITY(1,1),)
GO

None of it seems to work.

My statements run fine in SQL Server Management Studio, but in SAS Studio I get errors such as "expecting one of the following: ;, (, ',', '.', INDEX, TABLE, VIEW. "

So I tried different variations of adding ";" after the statement or after each command line, adding "RUN;", adding # in table name, using SAS table name structure (DATA.DATA) or SQL table name structure [DATA].[DATA].

I am running out of ideas to why this doesn't work.

Any help would be much appreciated

Kind regards,

Jacob

[stew90210 · Accepted solution]

hey!

the syntax for proc SQL is not identical to SQL so you need to spend a little time looking into it and correct your syntax, as you can see from the examples below they are not the same and this would not run in MYSQL:

proc sql;
SELECT state, sum(sales) as TOTSALES
FROM ussales
WHERE state in (’WI’,’MI’,’IL’)
GROUP BY state
HAVING sum(sales) > 40000
ORDER BY state desc;
quit;

[jbrau123]

Dear stew90210 (appreciate that reference 🙂 )

I am not familiar with MY-SQL, but the syntax you stated is T-SQL format. 

[sbxkoenk]

Hello,

Without having read your post in detail ... Have a look at the below (maybe it helps?).

SAS® 9.4 and SAS® Viya® 3.5 Programming Documentation | SAS 9.4 / Viya 3.5
SAS SQL Procedure User’s Guide
PROC SQL and the ANSI Standard
https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/sqlproc/p0corah7t43hy7n1r7efplde5c8z.htm

Differences in PROC SQL and ANSI SQL
https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/sqlproc/p0corah7t43hy7n1r7efplde5c8z.htm#p...

See also :
SAS® Viya® Programming Documentation | 2022.1.3
FedSQL Reference
High-Level Differences between the SQL Procedure and FedSQL
https://go.documentation.sas.com/doc/en/pgmsascdc/v_029/fedsqlref/p0infk9r96t8ybn1tgujpxqgmvrg.htm

Koen

[jbrau123]

Great, thanks!

[Tom]

SQL implementations vary.  PROC SQL tries to implement the 1992 SQL ANSI standard.

The "code" you posted does not look like valid ANSI SQL at all, even more modern versions.  What the heck is a the GO keyword supposed to mean?  Also what is the meaning of those brackets and pound sign characters?

You should check the documentation for T-SQL and see where its implementation has added things that are not included in the ANSI standard.