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
