topic Re: Syntax differences between PROC FEDSQL vs PROC SQL in SAS Programming

Syntax differences between PROC FEDSQL vs PROC SQL

phopkinson — Wed, 17 Jul 2024 09:53:01 GMT

Hi all,

I am trying to leverage some of the benefits of proc fedsql and cas, however I am facing some issues with the syntax in comparison, previously when i tried to filter rows using proc sql I just used a where clause, however when i do the same using proc fedsql, I get a error saying the column name exist for the filter value I am trying to apply:

WHERE VAR = "PROD";

It will return an error saying the column "PROD" does not exist or cannot be accessed, so I just want to understand the syntax difference that is more than likely causing this.

Re: Syntax differences between PROC FEDSQL vs PROC SQL

sbxkoenk — Wed, 17 Jul 2024 10:58:47 GMT

Weird ... because nothing is wrong with

WHERE VAR = "PROD";

Maybe you need to show us the full program.

Are you using SAS 9.4 or SAS Viya 3.5 or SAS Viya 4?
Are you using PROC FEDSQL on the CAS server (with CAS tables) or against an external database (like Oracle)?

Proc SQL or Proc FEDSQL: Which Should a Programmer Use? Q&A, Slides, and On-Demand Recording
https://communities.sas.com/t5/Ask-the-Expert/Proc-SQL-or-Proc-FEDSQL-Which-Should-a-Programmer-Use-Q-amp-A/ta-p/716702
Paper 3734-2018
Anything You Can Do I Can Do Better: PROC FEDSQL VS PROC SQL
Cuyler R. Huffman (1,2), Matthew M. Lypka (1), Jessica L. Parker (1)
(1) Spectrum Health Office of Research, (2) Grand Valley State University
https://support.sas.com/resources/papers/proceedings19/3734-2019.pdf

Koen

Re: Syntax differences between PROC FEDSQL vs PROC SQL

phopkinson — Thu, 18 Jul 2024 07:51:01 GMT

Yes, for some reason it didn't like the double quotes, when I changed it to single quotes it seemed to run ok.

Re: Syntax differences between PROC FEDSQL vs PROC SQL

Mazi — Thu, 18 Jul 2024 07:55:53 GMT

Glad you were able to resolve this.

I just wanted to note that Proc fedsql is a SAS proprietary implementation of ANSI SQL 1999. Which is why the double quotes do not work. Additionally, you may notice that other SAS enhancements such as the calculated keyword that is often used in Proc sql is not supported in fedsql.

Re: Syntax differences between PROC FEDSQL vs PROC SQL

phopkinson — Thu, 18 Jul 2024 09:18:04 GMT

I have realised that whilst I get no error and I have now run into a different problem - it returns no rows where the clause has actually been met.

Re: Syntax differences between PROC FEDSQL vs PROC SQL

phopkinson — Thu, 18 Jul 2024 12:17:25 GMT

I realised the macro variable I was using as my condition for filtering wasn't resolving with either single or double quotes, so put %TSLIT around it and it worked.

Re: Syntax differences between PROC FEDSQL vs PROC SQL

Mazi — Thu, 18 Jul 2024 12:20:43 GMT

Are you using CAS actions or the procedure?

Re: Syntax differences between PROC FEDSQL vs PROC SQL

phopkinson — Thu, 18 Jul 2024 13:47:10 GMT

The procedure