Could you please help me understand what I'm missing here? May I know which argument is invalid?
4578 (case when NEW_RENEWAL_DAY_MONTH_YEAR=REPORTING_DATE_ddmon then input("&reporting_date.",date9.)
SYMBOLGEN: Macro variable REPORTING_DATE resolves to 30JUN2018
14579 when input(cats(NEW_RENEWAL_DAY_MONTH_YEAR,"2018"),date9.) > today() then
14579 ! input(cats(NEW_RENEWAL_DAY_MONTH_YEAR,"2018"),date9.)
14580 else input(cats(NEW_RENEWAL_DAY_MONTH_YEAR,"2019"),date9.) end) as PODVER length = 8
14581 format = date5.
14582 from &SYSLAST
SYMBOLGEN: Macro variable SYSLAST resolves to WORK.W728Z2TN
14583 where case when NEW_RENEWAL_DAY_MONTH_YEAR=REPORTING_DATE_ddmon then "&reporting_date."d
SYMBOLGEN: Macro variable REPORTING_DATE resolves to 30JUN2018
14584 when input(cats(NEW_RENEWAL_DAY_MONTH_YEAR,"2018"),date9.) < today() then
14584 ! input(cats(NEW_RENEWAL_DAY_MONTH_YEAR,"2018"),date9.)
14585 else input(cats(NEW_RENEWAL_DAY_MONTH_YEAR,"2019"),date9.) end
14586 ;
NOTE: Invalid month value
NOTE: Invalid argument to function INPUT. Missing values may be generated.
NOTE: Invalid month value
NOTE: Invalid argument to function INPUT. Missing values may be generated.
NOTE: Table WORK.W729IB79 created, with 0 rows and 43 columns.
The log tells you:
NOTE: Invalid month value
NOTE: Invalid argument to function INPUT. Missing values may be generated.
As we cannot see your data we cannot debug this, look at your data, find the invalid month value{s} and correct for this. I would guess:
NEW_RENEWAL_DAY_MONTH_YEAR
Is the problem
I've data as follows and I've tested the code using data step already.
data test;
renewal_date='09mar';
output;
renewal_date='09nov';
output;
renewal_date='19sep';
output;
run;
proc sql;
create table test1 as select
case
when renewal_date="19sep" then "19sep2019"d
when input(cats(renewal_date,"2018"),date9.) > today() then input(cats(renewal_date,"2018"),date9.)
else input(cats(renewal_date,"2019"),date9.)
end
as new_renewal_date
from test;
quit;
data test2;
set test1;
format new_renewal_date date9.;
run;
Inorder to replicate the same logic in DI studio under expression, I wrote case when as mentioned in the log below. One change is I've hard coded (19Sep) the date value in data step whereas I've substitute the respective date variable (REPORTING_DATE_ddmon) in following expression.
17069 (case when NEW_RENEWAL_DAY_MONTH_YEAR=REPORTING_DATE_ddmon then input("&reporting_date.",date9.)
SYMBOLGEN: Macro variable REPORTING_DATE resolves to 30JUN2018
17070 when input(cats(NEW_RENEWAL_DAY_MONTH_YEAR,"2018"),date9.) > today() then
17070 ! input(cats(NEW_RENEWAL_DAY_MONTH_YEAR,"2018"),date9.)
17071 else input(cats(NEW_RENEWAL_DAY_MONTH_YEAR,"2019"),date9.) end) as PODVER length = 8
17072 format = date9.
17073 from &SYSLAST
SYMBOLGEN: Macro variable SYSLAST resolves to WORK.W728Z2TN
17074 where case when NEW_RENEWAL_DAY_MONTH_YEAR=REPORTING_DATE_ddmon then "&reporting_date."d
SYMBOLGEN: Macro variable REPORTING_DATE resolves to 30JUN2018
17075 when input(cats(NEW_RENEWAL_DAY_MONTH_YEAR,"2018"),date9.) < today() then
17075 ! input(cats(NEW_RENEWAL_DAY_MONTH_YEAR,"2018"),date9.)
17076 else input(cats(NEW_RENEWAL_DAY_MONTH_YEAR,"2019"),date9.) end
17077 ;
NOTE: Invalid month value
NOTE: Invalid argument to function INPUT. Missing values may be generated.
NOTE: Invalid month value
NOTE: Invalid argument to function INPUT. Missing values may be generated.
NOTE: Table WORK.W729IB79 created, with 0 rows and 43 columns.
All I can do is re-iterate what I said previously, the log is telling you:
NOTE: Invalid month value
NOTE: Invalid argument to function INPUT. Missing values may be generated.
herefore you need to look at your data and find which invalid data is being fed into the input statement. My guess is still
NEW_RENEWAL_DAY_MONTH_YEAR
However I cannot see your data (and posting some example data which works doesn't help I am afraid, you need to look at the data used in the SQL).
And you have still not shown any actual value for ANY of the variables involved in the calculations.
I agree with RW9 that it must be NEW_RENEWAL_DAY_MONTH_YEAR. The confusion starts with misleading variable names: Doesn't this name suggest that the variable contains a year portion? If so, how could it be equal to a variable named ..._ddmon or would it make sense to concatenate it with "2018" etc.?
Moreover, the WHERE condition looks unusual: a SAS date value used as a Boolean expression (i.e., it doesn't matter what date it is, only whether or not it's missing or 0).
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: