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
Dear stew90210 (appreciate that reference 🙂 )
I am not familiar with MY-SQL, but the syntax you stated is T-SQL format.
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
Great, thanks!
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.