errors in a sql query

Reply
Frequent Contributor
Posts: 81

errors in a sql query

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

Super Contributor
Posts: 490

Re: errors in a sql query

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


Frequent Contributor
Posts: 81

Re: errors in a sql query

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;

Valued Guide
Posts: 3,208

Re: errors in a sql query

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)

---->-- ja karman --<-----
Frequent Contributor
Posts: 81

Re: errors in a sql query

Hi Jaap,

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

regards

Anand

Trusted Advisor
Posts: 1,228

Re: errors in a sql query

What summary function are you using with group by?

Valued Guide
Posts: 3,208

Re: errors in a sql query

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?   

---->-- ja karman --<-----
Frequent Contributor
Posts: 81

Re: errors in a sql query

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

Super Contributor
Posts: 490

Re: errors in a sql query

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

Valued Guide
Posts: 3,208

Re: errors in a sql query

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)

---->-- ja karman --<-----
Frequent Contributor
Posts: 81

Re: errors in a sql query

ok. thanks Jaap for the help.

I will follow the user's guide.

Ask a Question
Discussion stats
  • 10 replies
  • 2181 views
  • 6 likes
  • 4 in conversation