Connecting SQL Table in SAS Viya

[Santha]

Hi

I am new to SAS Viya. 

I have a caslib called "X". I want to load a SQL table from another library into X with filters applied (meaning specific records from SQL only). I have this code below.

proc casutil;

load casdata="MSD" incaslib="LMENG" outcaslib="X" casout="MSDFiltered"
where="MasterClientName='XXX' and mode='Ocean' and YearQuarter='FY19Q3'";
promote;

run;

But I get an error saying the following . Any help to fix this is appreciated. Thank you.

"ERROR: Error occurred during Post Optimization.

ERROR: ERROR: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near the keyword 'Top'.

ERROR: ERROR: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The batch could not be analyzed because of compile

errors.

ERROR: Function failed.

ERROR: The action stopped due to errors.

NOTE: The Cloud Analytic Services server processed the request in 0.208456 seconds.

81 promote;

ERROR: Parameter 'name' is required but was not specified.

ERROR: The action stopped due to errors.

NOTE: The Cloud Analytic Services server pr

[ChrisNZ]

I would start by tracing the query. See SAS Data Connector/SAS Data Connect Accelerator Logging

[Santha]

Hi

I read it and i think it is a syntax issue that I don't get it. I am not able to figure out what part of  my Where clause is wrong. I was able to import the Full Table Called OceanRatingQueries. BUt what i really need is only when the Category says "terminal". Here is my code

proc casutil;
load casdata="OceanRatingQueries" incaslib="LMENG" outcaslib="STDOPT" casout="OceanRatingQueries";
WHERE="Category='Terminal'";
promote;
run;

[Tom]

You seem to have an extra semi-colon in the middle of your LOAD statement.

[Santha]

I have tried differen options like with and without semi colon. 

When I remove the semi colon at the end of load statement i get a different error. Here is the details of it.

77 load casdata="OceanRatingQueries" incaslib="LMENG" outcaslib="STDOPT" casout="OceanRatingQueries"

78 WHERE="Category='Terminal'";

NOTE: Performing serial LoadTable action using SAS Data Connector to SQL Server.

ERROR: Error occurred during Post Optimization.

ERROR: ERROR: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near the keyword 'or'.

ERROR: ERROR: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The batch could not be analyzed because of compile

errors.

ERROR: Function failed.

ERROR: The action stopped due to errors.

NOTE: The Cloud Analytic Services server processed the request in 0.179257 seconds.

79 promote;

ERROR: Parameter 'name' is required but was not specified.

ERROR: The action stopped due to errors.

NOTE: The Cloud Analytic Services server processed the request in 0.000972 seconds.

80 run;

[ChrisNZ]

You had

   ERROR: ERROR: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near the keyword 'Top'.

Now you have

   ERROR: ERROR: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near the keyword 'or'.

1. Why the difference? What did you change?

2. These keywords are not in the query shown.

3. Have you enabled tracing?