BookmarkSubscribeRSS Feed
Afor910327
Obsidian | Level 7

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 ;

 

2 REPLIES 2
Astounding
PROC Star

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.

Kurt_Bremser
Super User

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

 

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 12527 views
  • 2 likes
  • 3 in conversation