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;
