topic Re: errors in a sql query in SAS Programming

errors in a sql query

AnandSahu — Sat, 18 Oct 2014 11:58:47 GMT

Hi All,

I am getting errors mentioned below while running an SQL query so can anybody please come up with the solution. I can not provide the code as its about 700 lines.

NOTE: A GROUP BY clause has been discarded because neither the SELECT clause nor the optional HAVING clause of the associated

table-expression referenced a summary function.

NOTE: The query requires remerging summary statistics back with the original data.

regards

Anand

Re: errors in a sql query

mohamed_zaki — Sat, 18 Oct 2014 12:27:50 GMT

You can attach the code if you do not want to type in the feed

or provide enough part from the log.

You may also check:

Usage Note 4308: REMERGING SUMMARY STATISTICS note using PROC SQL

Grouping without Summarizing

Re: errors in a sql query

AnandSahu — Sat, 18 Oct 2014 12:41:32 GMT

here is the log:

NOTE: SAS Initialization used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

NOTE: The autoexec file, /opt/sas/config/Lev1/SASApp/WorkspaceServer/autoexec.sas, was executed at server initialization.

ERROR: The submitted line exceeds maximum line length - 32768 bytes.

1 ;*';*";*/;quit;run;

NOTE: SAS set option OBS=0 and will continue to check statements. This might cause NOTE: No observations in data set.

NOTE: PROCEDURE PRINTTO used (Total process time):

real time 0.04 seconds

cpu time 0.05 seconds

NOTE: Libref MSSQLLIB was successfully assigned as follows:

Engine: ODBC

Physical Name: mssqldynamics

NOTE: Libref LASRLIB was successfully assigned as follows:

Engine: SASIOLA

Physical Name: SAS LASR Analytic Server engine on local host, port 10031

NOTE: The "<>" operator is interpreted as "not equals".

NOTE: SQL view WORK.TEMP_LASR_VIEW_5834 has been defined.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.04 seconds

cpu time 0.05 seconds

NOTE: A GROUP BY clause has been discarded because neither the SELECT clause nor the optional HAVING clause of the associated

table-expression referenced a summary function.

2 The SAS System 13:08 Friday, October 17, 2014

NOTE: A GROUP BY clause has been discarded because neither the SELECT clause nor the optional HAVING clause of the associated

table-expression referenced a summary function.

NOTE: A GROUP BY clause has been discarded because neither the SELECT clause nor the optional HAVING clause of the associated

table-expression referenced a summary function.

NOTE: A GROUP BY clause has been discarded because neither the SELECT clause nor the optional HAVING clause of the associated

table-expression referenced a summary function.

NOTE: A GROUP BY clause has been discarded because neither the SELECT clause nor the optional HAVING clause of the associated

table-expression referenced a summary function.

NOTE: A GROUP BY clause has been discarded because neither the SELECT clause nor the optional HAVING clause of the associated

table-expression referenced a summary function.

NOTE: A GROUP BY clause has been discarded because neither the SELECT clause nor the optional HAVING clause of the associated

table-expression referenced a summary function.

NOTE: A GROUP BY clause has been discarded because neither the SELECT clause nor the optional HAVING clause of the associated

table-expression referenced a summary function.

NOTE: A GROUP BY clause has been discarded because neither the SELECT clause nor the optional HAVING clause of the associated

table-expression referenced a summary function.

NOTE: A GROUP BY clause has been discarded because neither the SELECT clause nor the optional HAVING clause of the associated

table-expression referenced a summary function.

NOTE: A GROUP BY clause has been discarded because neither the SELECT clause nor the optional HAVING clause of the associated

table-expression referenced a summary function.

NOTE: A GROUP BY clause has been discarded because neither the SELECT clause nor the optional HAVING clause of the associated

table-expression referenced a summary function.

NOTE: The query requires remerging summary statistics back with the original data.

NOTE: The data set LASRLIB.ISALESOVERALL_BF has 0 observations and 36 variables.

WARNING: Data set LASRLIB.ISALESOVERALL_BF was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

real time 6.85 seconds

cpu time 0.92 seconds

INFO: Registering A5KIUB9E.BF00068J to /Shared Data/SAS Visual Analytics/Public/Visual Analytics Public LASR library.

NOTE: PROCEDURE METALIB used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

NOTE: Line generated by the invoked macro "REGISTERTABLE".

2 omr ( library="&LIBRARY." &REPOSARG. );

___

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the invoked macro "REGISTERTABLE".

2 select ("&SELECTOBJ.");

______

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

3 ;*';*";*/;quit;run;

4 QUIT; RUN;

Re: errors in a sql query

jakarman — Sat, 18 Oct 2014 12:49:07 GMT

start with this one:

NOTE: The autoexec file, /opt/sas/config/Lev1/SASApp/WorkspaceServer/autoexec.sas, was executed at server initialization.

ERROR: The submitted line exceeds maximum line length - 32768 bytes.

1 ;*';*";*/;quit;run;

NOTE: SAS set option OBS=0 and will continue to check statements. This might cause NOTE: No observations in data set.

There is an error in your initialization. (autoexec config sas script)

Re: errors in a sql query

AnandSahu — Sat, 18 Oct 2014 12:53:42 GMT

Hi Jaap,

Any idea how, how can I eliminate this error????

regards

Anand

Re: errors in a sql query

stat_sas — Sat, 18 Oct 2014 12:57:50 GMT

What summary function are you using with group by?

Re: errors in a sql query

jakarman — Sat, 18 Oct 2014 19:32:50 GMT

The autoexec is executing by your workspace-server. From your answer I understand you are not the guy that did something there.

For the one doing that. First take take in the code of the autoexec in a standard SAS session an be sure there can be no error of what kind there.

As using Eguide is giving that little information, you could start using X11 a X-server in a DMS mode starting the script of the workspaceserver manually in a DMS mode.

For this one using the option -noautoexec and including the autoexec manually should give the debugging way for the errors on that part.

Could be anything as some typo, encoding issue as something with denied security on some datasets. What is done there?

Re: errors in a sql query

AnandSahu — Mon, 20 Oct 2014 04:31:16 GMT

Hi Jaap,

I am getting the above mentioned error in Create Query mode of SAS Visual Analytic and it uses the SAS LASR Analytic Server so could you please guide me how can I start the script in DMS Mode using the server SAS LASR Analytic. I has been working fine since last 2 months and I didn't make any changes.

-anand

Re: errors in a sql query

mohamed_zaki — Mon, 20 Oct 2014 04:56:35 GMT

You should start another discussions thread. Your current problem is not related to your original post anymore.

Re: errors in a sql query

jakarman — Mon, 20 Oct 2014 10:11:50 GMT

Anand, I did not get your problem right from start just seeing there are errors on the start on the WS.

Following the rule to solve the first seen error first. Others that are following thereafter only when it is obvious they are new ones not being caused by the first error.

You are using SAS VA and there is really as classic SAS WS and there is a metadataserver as it is build on top of those. Interacting with DI or AMO goes by using these servers/services.

It could be you just did not touch this part before and now does by using that SQL query node. This is not well documented just some hint there:

SAS(R) Visual Analytics 7.1: User's Guide (why is that intelligence platform data-administration guide mentioned?)

Validation of the installation should have been done with the WS. It is failing there.

The errors are coming with all references tot the WS server (not the LASR server)

Re: errors in a sql query

AnandSahu — Mon, 20 Oct 2014 12:00:09 GMT

ok. thanks Jaap for the help.

I will follow the user's guide.