Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Proc SQL codes are not working in SAS Visual Analytics

Reply
Contributor BSL
Contributor
Posts: 25

Proc SQL codes are not working in SAS Visual Analytics

Hi,

I am trying to run a logic in SAS VA but getting errors. I tried to achieve the result in two ways but its not working however the sample code is working fine on SAS EG.

The 1st code gives wrong output with adding all the values and 2nd code is giving errors the log and code are attached.

**************** 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;

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

SAMPLE QUERY:

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

data test;

input Date :date9. Shift$ IPRN;

format date date11.;

datalines;

05Jan2014 Day 12

05Jan2014 Day 11

05Jan2014 Day 10

05Jan2014 Night 07

05Jan2014 Night 09

06Jan2014 Day 08

06Jan2014 Day 12

06Jan2014 Night 07

06Jan2014 Night 09

06Jan2014 Night 11

;

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;

Attachment
Attachment
Ask a Question
Discussion stats
  • 0 replies
  • 291 views
  • 0 likes
  • 1 in conversation