Syntax differences between PROC FEDSQL vs PROC SQL

[phopkinson]

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.

[sbxkoenk]

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-...
• 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

[phopkinson]

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

[Mazi]

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.

[phopkinson]

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.  

[phopkinson]

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.

[Mazi]

Are you using CAS actions or the procedure?

[phopkinson]

The procedure