When inputting the underlined code below (towards the end), I get this error, any clues on what needs to be changed to take care of this?
Thank you
ERROR: Expression using subtraction (-) requires numeric types.
proc sql;
create table tempsas.temp_PI_&ioYear as
select riv.itmCode_id as item_id label="item_id"
, riv.itmCode as itemCode label="itemCode"
, riv.period as ioYear label="ioYear"
, riv.basePeriod as basingYear label="basingYear"
, riv.pRelGO as PI label="PI"
, oiv.bas * ic.weight as basicVal
, oiv.ctx * ic.weight as comTax
, ic.itemACPSA
, ic.itemACPSADescr
from sql_iips.rel_itmView riv
join sql_iips.out_itmView oiv
on riv.itmCode_id = oiv.itmCode_id
and riv.datasetPeriod_id = oiv.datasetPeriod_id
and riv.dataSet_id = oiv.dataSet_id
join tempsas.item_conc ic
on riv.itmCode_id = ic.itemCode_id
where riv.datasetName = &dsName
and riv.valType = 'DOM'
and oiv.period = "&ioYear"
and riv.period = &ioYear.
union
select riv.itmCode_id as item_id label="item_id"
, riv.itmCode as itemCode label="itemCode"
, riv.period as ioYear label="ioYear"
, riv.basePeriod as basingYear label="basingYear"
, riv.pRelGO as PI label="PI"
, oiv.bas * ic.weight as basicVal
, oiv.ctx * ic.weight as comTax
, ic.itemACPSA
, ic.itemACPSADescr
from sql_iips.rel_itmView riv
join sql_iips.out_itmView oiv
on riv.itmCode_id = oiv.itmCode_id
and riv.datasetPeriod_id = oiv.datasetPeriod_id
and riv.dataSet_id = oiv.dataSet_id
join tempsas.item_conc ic
on riv.itmCode_id = ic.itemCode_id
where riv.datasetName = 'ANNUAL14'
and riv.valType = 'DOM'
and oiv.period not in (select distinct period from sql_iips.out_itmView where datasetName = &dsName)
and riv.period not in (select distinct period from sql_iips.rel_itmView where datasetName = &dsName)
and %sysfunc(inputn(&ioyear.,8.)) - oiv.period = 1
and %sysfunc(inputn(&ioyear.,8.)) - riv.period = 1 ;
create table tempsas.PI_&ioYear as
select item_id
, itemCode
, ioYear
, basingYear
, PI
, basicVal
, comTax
, itemACPSA
, itemACPSADescr
from tempsas.temp_PI_&ioYear
union
select tpi.item_id
, tpi.itemCode
, tpi.ioYear
, tpi.basingYear
, tpi.PI
, sum(tpi.basicVal) as basicVal
, sum(tpi.comTax) as comTax
, sc.sumACPSA as itemACPSA label="itemACPSA"
, sc.sumACPSADescr as itemACPSADescr label="itemACPSADescr"
from tempsas.temp_PI_&ioYear tpi
join concord.&summaryConc sc
on tpi.itemACPSA = sc.itemACPSA
group by tpi.item_id, tpi.itemCode, tpi.ioYear, tpi.basingYear, tpi.PI, sc.sumACPSA, sc.sumACPSADescr
;
create table tempsas.PI2_&ioYear as
select item_id,
itemcode,
ioyear,
basingyear,
pi,
(basicVal/pi) as basicVal label="basicVal",
(comtax/pi) as comtax label="comTax",
itemACPSA,
itemACPSADescr
from tempsas.pi_&ioYear
;
create table tempsas.temp_output_&ioYear as
select oiv.itmCode_id as item_id label="item_id"
, oiv.itmCode as itemCode label="itemCode"
, oiv.prdnbr as ioYear label="ioYear"
, oiv.prdnbr as basingYear label="basingYear"
, 1.0 as PI
, oiv.bas * ic.weight as basicVal
, oiv.ctx * ic.weight as comTax
, ic.itemACPSA
, ic.itemACPSADescr
from sql_iips.out_itmView oiv
join tempsas.item_conc ic
on oiv.itmCode_id = ic.itemCode_id
where oiv.datasetName = &dsName
and oiv.period = "&ioYear"
union
select oiv.itmCode_id as item_id label="item_id"
, oiv.itmCode as itemCode label="itemCode"
, oiv.prdnbr as ioYear label="ioYear"
, oiv.prdnbr as basingYear label="basingYear"
, 1.0 as PI
, oiv.bas * ic.weight as basicVal
, oiv.ctx * ic.weight as comTax
, ic.itemACPSA
, ic.itemACPSADescr
from sql_iips.out_itmView oiv
join tempsas.item_conc ic
on oiv.itmCode_id = ic.itemCode_id
where oiv.datasetName = 'ANNUAL14'
and oiv.period not in (select distinct period from sql_iips.out_itmView where datasetName = &dsName)
and &ioyear. - oiv.prdnbr = 1 ;
create table tempsas.output_&ioYear as
select item_id
, itemCode
, ioYear
, basingYear
, PI
, basicVal
, comTax
, itemACPSA
, itemACPSADescr
from tempsas.temp_output_&ioYear
union
select a.item_id
, a.itemCode
, a.ioYear
, a.basingYear
, a.PI
, sum(a.basicVal) as basicVal
, sum(a.comTax) as comTax
, sc.sumACPSA as itemACPSA label="itemACPSA"
, sc.sumACPSADescr as itemACPSADescr label="itemACPSADescr"
from tempsas.temp_output_&ioYear a
join concord.&summaryConc sc
on a.itemACPSA = sc.itemACPSA
group by a.item_id, a.itemCode, a.ioYear, a.basingYear, a.PI, sc.sumACPSA, sc.sumACPSADescr
;
quit;
%end;
%mend prep_series;
%prep_series
