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

Afor910327 — Tue, 10 Oct 2017 17:02:36 GMT

Hi,

I keeping getting this error message below, I do not know why that comes up, oiv. and riv. are numberical, is there any way to make the sysfunc below numerical?

and %sysfunc(inputn(&ioyear.,8.)) - oiv.period = 1

and %sysfunc(inputn(&ioyear.,8.)) - riv.period = 1

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

%macro prep_series;

%do ioyear = &b_ioYear %to &e_ioyear;

**************************************************************************************************************;

******************************* Item concordance weight update **********************************************;

This section updates the weights in the item concordance for the item partials. Some of the itemCodes have weights

that change from year to year. To incorporate those into the ACPSA, the item concordance needs to be changed for

each processing year. The weights that don't change will remain as they were but the ones that do change will

be updated.

***************************************************************************************************************;

data tempsas.current_item_concordance;

set concord.&acpsaitemcc;

proc sort data=tempsas.current_item_concordance out=tempsas.current_item_concordance_sort;

by itemCode itemACPSA;

run;

data tempsas.itemACPSA_partial_combine_0;

length itemCode $ 20 itemDescr $ 300 itemACPSA $ 9 itemACPSADescr $ 90;

set tempsas.itemACPSA_23 tempsas.itemACPSA_3X tempsas.itemACPSA_51 tempsas.itemACPSA_54 tempsas.itemACPSA_56 tempsas.itemACPSA_61 tempsas.itemACPSA_71 tempsas.itemACPSA_81 tempsas.itemACPSA_99;

where upcase(isInterp) ne 'Y';

format itemCode $20. itemDescr $300. itemACPSA $9. itemACPSADescr $90.;

data tempsas.itemACPSA_partial_combine;

set tempsas.itemACPSA_partial_combine_0 tempsas.itemACPSA_Interp;

proc sort data=tempsas.itemACPSA_partial_combine out=tempsas.itemACPSA_partial_combine_sort;

by itemCode itemACPSA;

proc summary data=tempsas.current_item_concordance_sort;

by itemCode;

id itemDescr;

var weight;

output out=tempsas.current_item_concordance_sum(drop=_type_) sum= ;

run;

/* itemCode weight check */

proc summary data=tempsas.itemACPSA_partial_combine_sort;

by itemCode;

id itemDescr;

var y1997 - y2014;

output out=tempsas.itemACPSA_partial_summary(drop=_type_) sum= ;

run;

data tempsas.partial_item_ACPSA

tempsas.partial_all_other

tempsas.no_update (keep=itemCode_id itemCode itemDescr partial weight itemACPSA itemACPSADescr);

merge tempsas.current_item_concordance_sort(in=a) tempsas.itemACPSA_partial_combine_sort (in=b);

by itemCode;

if b=1 and itemACPSA ne 'item99' then output tempsas.partial_item_ACPSA;

else if b=1 and itemACPSA = 'item99' then output tempsas.partial_all_other;

else output tempsas.no_update;

run;

data tempsas.partial_item_ACPSA_format;

set tempsas.partial_item_ACPSA;

weight = y&ioyear;

keep itemCode_id itemCode itemDescr partial weight itemACPSA itemACPSADescr;

run;

proc sort data=tempsas.partial_item_ACPSA_format out=tempsas.partial_item_ACPSA_sort;

by itemCode_id itemCode;

proc summary data=tempsas.partial_item_ACPSA_sort;

by itemCode_id itemCode;

id itemDescr;

var weight;

output out=tempsas.partial_item_ACPSA_summary(drop=_type_) sum=;

run;

data tempsas.partial_ACPSA_sum1

tempsas.partial_ACPSA_not1;

set tempsas.partial_item_ACPSA_summary;

if weight < 0.999 then output tempsas.partial_ACPSA_not1;

else output tempsas.partial_ACPSA_sum1;

run;

proc sort data=tempsas.partial_ACPSA_not1 out=tempsas.partial_ACPSA_not1_sort;

by itemCode_id itemCode;

data tempsas.partial_not1_adj;

merge tempsas.partial_ACPSA_not1_sort(in=a rename=(weight=total_weight)) tempsas.partial_item_ACPSA_sort;

by itemCode_id;

if a=1;

weight = 1 - total_weight;

itemACPSA = 'item99';

itemACPSADescr = 'All Other Commodities';

drop _freq_ total_weight;

run;

proc sort data=tempsas.partial_not1_adj out=tempsas.partial_not1_adj_sort;

by itemCode_id itemCode itemDescr partial weight itemACPSA itemACPSADescr;

proc summary data=tempsas.partial_not1_adj_sort;

by itemCode_id itemCode itemDescr partial weight itemACPSA itemACPSADescr;

output out=tempsas.partial_not1_correction(drop=_type_ _freq_);

run;

data tempsas.partial_ACPSA_remerge;

set tempsas.partial_item_ACPSA_format tempsas.partial_not1_correction;

run;

***************** Checks the sum of partial ACPSA items. Weights should sum to 1. **************;

data tempsas.all_partials_remerged;

set tempsas.partial_ACPSA_remerge;

proc sort data=tempsas.all_partials_remerged out=tempsas.all_partials_remerged_sort;

by itemCode;

proc summary data=tempsas.all_partials_remerged_sort;

by itemCode;

id itemDescr;

var weight;

output out=tempsas.partial_ACPSA_sum_check(drop=_type_ _freq_) sum= ;

run; /*sbo 9.2.2016 - Check works for 2012 */

proc print data=tempsas.partial_ACPSA_sum_check noobs;

title3 "Partial ACPSA item weight check, &ioYear";

TITLE4 "%sysfunc(today(),weekdate.), %sysfunc(time(),time8.)";

var itemCode weight;

run;

****************************************************************************;

data tempsas.new_item_conc;

set tempsas.partial_ACPSA_remerge tempsas.no_update;

itemACPSADescr = propcase(itemACPSADescr);

proc sort data=tempsas.new_item_conc out=tempsas.new_item_conc_sort;

by itemCode itemACPSA;

*******************************************************************************************;

/* Check on new concordance to make sure weights sum to 1 */

proc summary data = tempsas.new_item_conc_sort;

by itemCode;

id itemDescr;

var weight;

output out=tempsas.new_item_conc_summary(drop=_type_ _freq_) sum= ;

run;

data tempsas.good_conc

tempsas.bad_conc;

set tempsas.new_item_conc_summary;

if weight ne 1 then output tempsas.bad_conc;

else output tempsas.good_conc;

run;

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

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

ACPSA item concordance

*****************************************************************************************************;

data tempsas.item_conc;

set tempsas.new_item_conc_sort;

itemACPSADescr = propcase(itemACPSADescr);

The following program creates three tables for used in calculating Laspeyres and Pasche quantity and price

indices. The methodology follows that of a program written by Gabriel Medeiros to calculate prices for annual

IO.

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

topic Re: ERROR: Expression using subtraction (-) requires numeric types. in SAS Procedures

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

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

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

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

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

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

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