Errors in Proq SQL codes

Accepted Solution Solved
Reply
Contributor BSL
Contributor
Posts: 25
Accepted Solution

Errors in Proq SQL codes

hi all,

I am getting some errors in the following codes could anybody please figure out the problem:

proc sql noprint;

    create table TEMP_LASR_VIEW_16210 as

    SELECT

        test_eff123.Iddate length=8 format=DATETIME22.3 AS Iddate,

        test_eff123.IdPlantCode length=1 format=$1. AS IdPlantCode,

        test_eff123.IdShiftCode length=1 format=$1. AS IdShiftCode,

        test_eff123.IdPrdCode length=8 format=6. AS IdPrdCode,

        test_eff123.ProdQty length=8 AS ProdQty,

        test_eff123.PrdBBpH length=8 format=12.3 AS PrdBBpH,

        test_eff123.InHrs length=8 AS InHrs,

        test_eff123.PrdName length=50 format=$50. AS PrdName,

        SUM(test_eff123.IdPlantRunHRs) length=8 format=12.2 AS IdPlantRunHRs_SUM

    FROM

        (select test_eff123.iddate, test_eff123.idshiftcode avg(test_eff123.idplantrunHRs) as idplntrunHRs from libbnpl.test_eff123 group by test_eff123.iddate, test_eff123.idshiftcode) as test_eff123

    GROUP BY

        test_eff123.Iddate,

        test_eff123.IdPlantCode,

        test_eff123.IdShiftCode,

        test_eff123.IdPrdCode,

        test_eff123.ProdQty,

        test_eff123.PrdBBpH,

        test_eff123.InHrs,

        test_eff123.PrdName;

quit;

In the above code, getting following errors:

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,

              CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

ERROR: File WORK.TEMP_LASR_VIEW_16210.DATA does not exist.

**however there is no proble with this table***

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

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

Regards

Rahul


Accepted Solutions
Solution
‎11-10-2014 08:57 AM
Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Errors in Proq SQL codes

The part within the brackets is called a sub-query.  Think of this as another step.  The output of this sub-query is passed back to the main query.  however you are using and alias to that subquery - test_eff123 - within the sub-query which is an error in itself.  The subquery will return a table with 3 variables as they are the only ones in the select of *the subquery* not the original table.  IdPlantCode is not within the select:

(select test_eff123.iddate, test_eff123.idshiftcode avg(test_eff123.idplantrunHRs) as idplntrunHRs from libbnpl.test_eff123 group by test_eff123.iddate, test_eff123.idshiftcode)

Hence it would not be available as part of reference test_eff123.

Take the sub-query out and put in SQL on its own:

proc sql;

     create table temp as

      select iddate, idshiftcode avg(idplantrunHRs) as idplntrunHRs

     from libbnpl.test_eff123

     group by iddate, idshiftcode;

quit;

You can then see what the outer query is basing its select on.  Note I drop the alias test_eff123 as that is not related to inner query.

Oh, and on the log, I meant post the actually query part of the log as well as that will normally point to here the error happens.

View solution in original post


All Replies
Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Errors in Proq SQL codes

Several errors I can see straight off.  You sum: test_eff123.IdPlantRunHRs

But this is not in the input table.  Also a lot of the other variables you select are not in the input table.

        (select test_eff123.iddate, test_eff123.idshiftcode avg(test_eff123.idplantrunHRs) as idplntrunHRs from libbnpl.test_eff123 group by test_eff123.iddate, test_eff123.idshiftcode) as test_eff123

Run this first and see what variables you have.  Also post the full log, i.e. showing where the error occurs,

Contributor BSL
Contributor
Posts: 25

Re: Errors in Proq SQL codes

The input table is libbnpl.test_eff123 and it has all the variable which are mentioned in select statement. I didnt get why you are saying so.

The full log is attached.

Solution
‎11-10-2014 08:57 AM
Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Errors in Proq SQL codes

The part within the brackets is called a sub-query.  Think of this as another step.  The output of this sub-query is passed back to the main query.  however you are using and alias to that subquery - test_eff123 - within the sub-query which is an error in itself.  The subquery will return a table with 3 variables as they are the only ones in the select of *the subquery* not the original table.  IdPlantCode is not within the select:

(select test_eff123.iddate, test_eff123.idshiftcode avg(test_eff123.idplantrunHRs) as idplntrunHRs from libbnpl.test_eff123 group by test_eff123.iddate, test_eff123.idshiftcode)

Hence it would not be available as part of reference test_eff123.

Take the sub-query out and put in SQL on its own:

proc sql;

     create table temp as

      select iddate, idshiftcode avg(idplantrunHRs) as idplntrunHRs

     from libbnpl.test_eff123

     group by iddate, idshiftcode;

quit;

You can then see what the outer query is basing its select on.  Note I drop the alias test_eff123 as that is not related to inner query.

Oh, and on the log, I meant post the actually query part of the log as well as that will normally point to here the error happens.

Contributor BSL
Contributor
Posts: 25

Re: Errors in Proq SQL codes

I tried the code in both ways..

The 1st code gives wrong ouptput with adding all the values

and 2nd code is still giving errors..

**************** CODE NO. 1 ****************

proc sql noprint;

    create table TEMP_LASR_VIEW_16210 as

      select iddate, IdPlantcode, IdshiftCode, IdPrdCode, ProdQty, PrdBBpH,

            InHrs, IdPlantRunHRs, avg(IdPlantRunHRs) format=12.2 AS IdPlantRunHr

      from libbnpl.test_eff123

     group by iddate, idshiftcode;

quit;

proc sql;

create table TEMP_LASR_VIEW_16215 AS

    select iddate, IdPlantcode, IdshiftCode, IdPrdCode, ProdQty, PrdBBpH, InHrs,

    sum(IdPlantRunHr) format=12.2 AS IdPlantRunHRss

    from TEMP_LASR_VIEW_16210

    group by iddate;

quit;

**************** CODE NO. 2 ****************

proc sql;

    create table TEMP_LASR_VIEW_16210 as

    SELECT

        Iddate length=8 format=DATETIME22.3 AS Iddate,

        IdPlantCode length=1 format=$1. AS IdPlantCode,

        IdShiftCode length=1 format=$1. AS IdShiftCode,

        IdPrdCode length=8 format=6. AS IdPrdCode,

        ProdQty length=8 AS ProdQty,

        PrdBBpH length=8 format=12.3 AS PrdBBpH,

        InHrs length=8 AS InHrs,

        PrdName length=50 format=$50. AS PrdName,

        SUM(idplntrunHRs_AVG) length=8 format=12.2 AS IdPlantRunHRs_SUM

    FROM

        (select iddate, PrdName IdPlantcode, IdshiftCode, IdPrdCode, ProdQty, PrdBBpH, InHrs,

          avg(IdPlantRunHRs) format=12.2 as idplntrunHRs_AVG

          from libbnpl.test_eff123

          group by iddate, idshiftcode);

    GROUP BY

        Iddate,

        IdPlantCode,

        IdShiftCode,

        IdPrdCode,

        ProdQty,

        PrdBBpH,

        InHrs,

        PrdName;

quit;

*********************************

however my sample query is working fine in EG but I am trying to apply it in SAS Visual Analytics.

SAMPLE QUERY:

data test;

input Date :date9. Shift$ IPRN;

format date date11.;

datalines;

05Jan2014 Day 12

05Jan2014 Day 11

05Jan2014 Day 10      11

05Jan2014 Night 07

05Jan2014 Night 09    8

06Jan2014 Day 08

06Jan2014 Day 12       10

06Jan2014 Night 07

06Jan2014 Night 09

06Jan2014 Night 11     9

;

run;

proc sql;

create table want as

select date,sum(iprn) as iprn

from (select date, shift,avg(iprn) as iprn from test group by date,shift)

  group by  date;

quit;

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Errors in Proq SQL codes

Well, to start with in Code 2 you have a semicolon after the sub-query.  In the first example you do not restrict the input data at all.  Take the semicolon out of the second example, then run it.  If there are errors please post the full log including the proc sql code as well as SAS will put an indicator of where it thinks it has gone wrong (not always accurate, but better than nothing).

Contributor BSL
Contributor
Posts: 25

Re: Errors in Proq SQL codes

Its not working even after removing semi-colon from sub-query. I am using this code in SAS Visual Analytics so is that a problem???

log and complete code is attached.

Attachment
Attachment
Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Errors in Proq SQL codes

There is a missing comma in the subquery also:

     (select iddate, PrdName ***missing comma*** IdPlantcode, IdshiftCode, IdPrdCode, ProdQty, PrdBBpH, InHrs,

          avg(IdPlantRunHRs) format=12.2 as idplntrunHRs_AVG

          from libbnpl.test_eff123

Try that.  If not the query looks ok to me so it may well be VA, I note there are DB connections etc - I presume all these work correctly?  I don't use this myself so can only look from Base SAS point of view.  The log is of zero help I am afraid.  Perhaps re-post your message in SAS VA community.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 329 views
  • 0 likes
  • 2 in conversation