BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
EW1
Calcite | Level 5 EW1
Calcite | Level 5

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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

3 REPLIES 3
novinosrin
Tourmaline | Level 20

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 

 

 

EW1
Calcite | Level 5 EW1
Calcite | Level 5
thank you so much for your advice.. it works for me..
Astounding
PROC Star

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.

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
  • 3 replies
  • 731 views
  • 1 like
  • 3 in conversation