BookmarkSubscribeRSS Feed
phopkinson
Obsidian | Level 7

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.

7 REPLIES 7
sbxkoenk
SAS Super FREQ

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)?

 

Koen

phopkinson
Obsidian | Level 7

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

Mazi
Pyrite | Level 9
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
Obsidian | Level 7

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
Obsidian | Level 7

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
Pyrite | Level 9
Are you using CAS actions or the procedure?

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 968 views
  • 5 likes
  • 3 in conversation