Hello all,
I am getting this error below on my %sysfunc part, does anyone know what is going on?
Thanks!
The INPUT function referenced in the %SYSFUNC or %QSYSFUNC macro function is not found
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(input(&ioyear.,8.)) - oiv.period = 1
and %sysfunc(input(&ioyear.,8.)) - riv.period = 1 ;
What is in &IOYEAR that you need to apply any functions at all? Can't you just use:
and &ioyear. - oiv.period = 1
and &ioyear. - riv.period = 1 ;
At any rate, %SYSFUNC cannot be used with INPUT. You would have to switch to INPUTN or INPUTC instead.
The input() function is one of those not available with %sysfunc and %qsysfunc (see http://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.2&docsetId=mcrolref&docsetTarget=p1o1...)
It's also not necessary, as the macro language has only one data type, text.
So your condition should just read:
and &ioyear. - oiv.period = 1
so if macro variable ioyear contains 2017, the macro preprocessor will render
and 2017 - oiv.period = 1
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.