topic Re: Connecting SQL Table in SAS Viya in SAS Programming

Connecting SQL Table in SAS Viya

Santha — Mon, 18 Nov 2019 19:01:20 GMT

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

Re: Connecting SQL Table in SAS Viya

ChrisNZ — Tue, 19 Nov 2019 08:19:19 GMT

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

Re: Connecting SQL Table in SAS Viya

Santha — Wed, 20 Nov 2019 19:02:48 GMT

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;

Re: Connecting SQL Table in SAS Viya

Tom — Wed, 20 Nov 2019 19:49:00 GMT

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

Re: Connecting SQL Table in SAS Viya

Santha — Wed, 20 Nov 2019 19:54:23 GMT

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;

Re: Connecting SQL Table in SAS Viya

ChrisNZ — Thu, 21 Nov 2019 03:01:06 GMT

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?