topic Re: The INPUT function referenced in the %SYSFUNC or %QSYSFUNC macro function is not found in SAS Programming

The INPUT function referenced in the %SYSFUNC or %QSYSFUNC macro function is not found

Afor910327 — Tue, 10 Oct 2017 13:18:51 GMT

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 ;

Re: The INPUT function referenced in the %SYSFUNC or %QSYSFUNC macro function is not found

Astounding — Tue, 10 Oct 2017 13:25:59 GMT

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.

Re: The INPUT function referenced in the %SYSFUNC or %QSYSFUNC macro function is not found

Kurt_Bremser — Tue, 10 Oct 2017 13:27:02 GMT

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=p1o13d7wb2zfcnn19s5ssl2zdxvi.htm&locale=de#n0x97dl6jtc5ujn1iowwm5bp8p4m)

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