ERROR: Expression using subtraction (-) requires numeric types.

Afor910327 — Tue, 10 Oct 2017 13:41:30 GMT

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

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

Re: ERROR: Expression using subtraction (-) requires numeric types.

Astounding — Tue, 10 Oct 2017 13:54:31 GMT

Can you check whether OIV.PERIOD and RIV.PERIOD are actually defined as numeric? SAS won't perform math on characters.

Re: ERROR: Expression using subtraction (-) requires numeric types.

Afor910327 — Tue, 10 Oct 2017 15:04:17 GMT

Astounding,

There are both numeric, confirmed.

topic Re: ERROR: Expression using subtraction (-) requires numeric types. in All Things Community

ERROR: Expression using subtraction (-) requires numeric types.

Re: ERROR: Expression using subtraction (-) requires numeric types.

Re: ERROR: Expression using subtraction (-) requires numeric types.