topic Re: New SAS User - How do change text field to numeric and using Input function in SAS Programming

New SAS User - How do change text field to numeric and using Input function

EW1 — Mon, 08 Jan 2018 17:03:06 GMT

Hello community,

I'm a beginner SAS user ( version 9.4) and I'm trying to calculate age in days. I'm trying to change a text field to a numeric. Here's the formula that I'm using [ today() - input(ad.trans_date,10.) as age_in_days format yymmdd10.,]The error message says that it's an invalid string and has an invalid argument and missing values may be generated.

Below is the full code to my process.

proc sql;

create table Annuity AS

select

c.Cust_Uniqe_No as TaxIDSSN,

ad.Policy_No,

upcase(compbl(trimn(ci.last_name)||', '||trimn(ci.first_name)||' '||trimn(ci.middle_name))) as customer_name,

ad.Trans_date,

ad.Premium_Amt as Amount,

f.Fund_Name as Product,

today() - input(ad.trans_date,10.) as age_in_days format yymmdd10.,

upcase(compbl(trimn(ud.Last_Name)||", "||trimn(ud.First_Name))) as agent_name,

ad.Cancel_Date

From wealth.Appointment a

inner join wealth.Annuity_Detail ad on a.Appnt_ID = ad.Appnt_ID

inner join wealth.Customer c on c.Cust_ID = a.Cust_ID

inner join wealth.Fund f on f.Fund_ID = ad.Fund_ID

inner join wealth.Customer_Individual ci on c.Cust_id = ci.Cust_ID

inner join wealth.User_Detail ud on ud.Adv_Id = a.Adv_Id

Where (ad.policy_no in ('',' ') or ad.policy_no is null)

Order by ad.Trans_date;

quit;

Re: New SAS User - How do change text field to numeric and using Input function

novinosrin — Mon, 08 Jan 2018 17:07:27 GMT

You need a date informat instead of 10. to convert your char date to numeric date and then do the age computation. Please check the appropriate date informat to apply in the input function

Usually date informats are like mirror of date formats, you would need something like

input(ad.trans_date,yymmdd10.)

Check your char date in your source data thoroughly

Re: New SAS User - How do change text field to numeric and using Input function

Astounding — Mon, 08 Jan 2018 17:23:33 GMT

Secondary consideration ... it is a mistake to assign a date format to AGE_IN_DAYS. Just leave it as a numeric field.

If you have any trouble applying the @novinosrin suggestion, show us what your incoming values for TRANS_DATE look like. That's the key information that would determine whether you need a more specific answer to your question.

Re: New SAS User - How do change text field to numeric and using Input function

EW1 — Mon, 08 Jan 2018 17:29:17 GMT

thank you so much for your advice.. it works for me..