- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Yes, for some reason it didn't like the double quotes, when I changed it to single quotes it seemed to run ok.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
The procedure