DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
New Contributor EW1
New Contributor
Posts: 2
Accepted Solution

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

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;

 


Accepted Solutions
Solution
‎01-08-2018 12:26 PM
PROC Star
Posts: 1,349

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

[ Edited ]

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 

 

 

View solution in original post


All Replies
Solution
‎01-08-2018 12:26 PM
PROC Star
Posts: 1,349

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

[ Edited ]

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 

 

 

New Contributor EW1
New Contributor
Posts: 2

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

Posted in reply to novinosrin
thank you so much for your advice.. it works for me..
Super User
Posts: 6,543

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

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 121 views
  • 1 like
  • 3 in conversation