Calculating age from two date string variables in YYYYMMDD

[SASPreK]

I have the following dataset

data have;
   input dob   sample_dt ;
datalines;
19650214    20100429
19800724    20210823
19991208    20090908
;

I want to calculate the age in years based on dob and sample_dt. Looking for the following output

dob                  sample_dt     age
19650214       20100429      45
19800724      20210623      40
19991208       20090908     09

I tried the following code but its not working.

data want;
set have;
dob=input(dob, yymmdd8.);
sample_dt=input(sample_dt, yymmdd8.);
age=yrdif(dob, sample_dt);
format dob sample_dt yymmdd8.;
run;

[ballardw]

Not working" is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "</>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "</>" icon or attached as text to show exactly what you have and that we can test code against.

Once a variable is created in SAS you cannot change its' type. Since DOB and Sample_dt are character the INPUT to "change" them to numeric date values fails.

Also your first data step does not create the variables as character causing other issues as well.

So create new variables from the character versions.

data have;
   input dob $  sample_dt $ ;
datalines;
19650214    20100429
19800724    20210823
19991208    20090908
;

data want;
   set have;
   newdob=input(dob, yymmdd8.);
   newsample_dt=input(sample_dt, yymmdd8.);
   age=yrdif(newdob, newsample_dt);
   format newdob newsample_dt yymmdd8.;
run;

If you want age to be an integer then use to remove the decimal portion of age.

age= floor (yrdif(newdob, newsample_dt) );

[SASPreK]

So sorry for not providing complete information and thank you for responding. The solution that you have provided works for the test dataset that was created for this question. However, I am getting the following error in my original dataset.

ERROR 48-59: The format $YYMMDD was not found or could not be loaded.

Please see the log below that has the similar kind of code.

264 data labcd4age /*labcd4anly labcd4_agelt13*/;
265 set lab_cd4anly1;
266 birth_dt=input(dob,yymmdd8.);
267 collectdt=input(sample_dt, yymmdd8.);
268 format birth_dt collectdt yymmdd8.;
269 age=yrdif(birth_dt, collectdt);

270 format dob collectdt yymmdd8.;

--------
48
ERROR 48-59: The format $YYMMDD was not found or could not be loaded.

271 /*if age>=13 then output labcd4anly; else
272 output labcd4_agelt13;*/
273 run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.LABCD4AGE may be incomplete. When this step was stopped there were 0
observations and 15 variables.
WARNING: Data set WORK.LABCD4AGE was not replaced because this step was stopped.

Please note these are the variable properties in the original dataset

Alphabetic List of Variables and Attributes

# Variable Type Len   Format Informat Label

10 dob            Char 8    $8.         $8.              dob
3  sample_dt Char 8    $8.        $8.              sample_dt

[ballardw]

269 age=yrdif(birth_dt, collectdt);

270 format dob collectdt yymmdd8.;


--------
48
ERROR 48-59: The format $YYMMDD was not found or could not be loaded.

The dollar sign in front of the YYMMDD means that at least one of the variables you attempted to format is character. Character variables cannot use numeric formats and this will happen when you try to assign a numeric format to a character variable. Looks like DOB is the culprit. Remove it from the Format statement or remove that entire Format statement if not needed.

[SASPreK]

I removed the formats and ran the following code 

data labcd4age /*labcd4anly labcd4_agelt13*/;
set lab_cd4anly1;
age=yrdif(dob, sample_dt);
/*if age>=13 then output labcd4anly; else
output labcd4_agelt13;*/
run;

And got the following error

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
100:15 100:20
NOTE: Invalid argument to function YRDIF(19650214,20100429) at line 100 column 9.
sample_dt=20100429 dob=19650214 age=. _ERROR_=1 _N_=1

NOTE: Mathematical operations could not be performed at the following places. The results of the
operations have been set to missing values.
Each place is given by: (Number of times) at (Line):(Column).
7272426 at 100:9

[Tom]

You created the numeric variable BIRTH_DT from the character variable DOB.

266 birth_dt=input(dob,yymmdd8.);

But then tried to attach a numeric format to DOB.

270 format dob collectdt yymmdd8.;

That should be BIRTH_DT in the format statement instead of DOB.

[Kurt_Bremser]

In the FORMAT statement, use birth_dt instead of dob. dob is character (as there is no NOTE about an automatic conversion when you use it in the INPUT function).

[SASPreK]

Thank you for catching that. I changed it to birth_dt and got the following error:
98 data labcd4age /*labcd4anly labcd4_agelt13*/;
99 set lab_cd4anly1;
100 birth_dt=input(dob,yymmdd8.);
101 collectdt=input(sample_dt, yymmdd8.);
102 format birth_dt collectdt yymmdd8.;
103 age=yrdif(birth_dt, collectdt);
104 format birth_dt collectdt yymmdd8.;
105 /*if age>=13 then output labcd4anly; else
106 output labcd4_agelt13;*/
107 run;

NOTE: Invalid argument to function YRDIF(.,18431) at line 103 column 9.

[Kurt_Bremser]

If there is no NOTE for invalid data, then the most likely cause is an empty dob variable. An empty string is a valid input and results in a missing value.

[SASPreK]

Thank you for the info. However, my dob variable does not seem to be empty in the dataset. Also, the log shows otherwise, see below the bold part.

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
100:15 100:20
NOTE: Invalid argument to function YRDIF(19650214,20100429) at line 100 column 9.
sample_dt=20100429 dob=19650214 age=. _ERROR_=1 _N_=1

Please see this was another note 

NOTE: Mathematical operations could not be performed at the following places. The results of the
operations have been set to missing values.
Each place is given by: (Number of times) at (Line):(Column).
7272426 at 100:9

[Kurt_Bremser]

---

@SASPreK wrote:

Thank you for the info. However, my dob variable does not seem to be empty in the dataset. Also, the log shows otherwise, see below the bold part.

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
100:15 100:20
NOTE: Invalid argument to function YRDIF(19650214,20100429) at line 100 column 9.
sample_dt=20100429 dob=19650214 age=. _ERROR_=1 _N_=1

Please see this was another note 

NOTE: Mathematical operations could not be performed at the following places. The results of the
operations have been set to missing values.
Each place is given by: (Number of times) at (Line):(Column).
7272426 at 100:9

---

Your arguments to the YRDIF function are not valid SAS dates. SAS dates are counts of days, with 1960-01-01 as day zero.

[Tom]

It is going to be hard to help you if you don't provide complete information.

What is the code on line 100 that is using character expressions where numeric expressions are needed?  Does it have anything to do with the two variables whose values you show?

If SAMPLE_DT and DOB are character variables then they are definitely not DATE values since DATE values are numbers.   If you tried to use them as if they were dates you would get that message about converting character strings to numbers.

And if they are numeric variables then they are also definitely not date values (unless you decided to confuse the poor humans that have to look at the date values by attaching the YYMMDDN8. format to them so they print as just a string of digits without any separator characters between the year month and day of the month parts).  A date value is going to be in the 20K range not the 20M range. So number like 20,100,429. might look to a human like it is intended to be interpreted as yy,yym,mdd date digits.  But SAS won't think that..  The number SAS uses to store April 29, 2010 is the number 18,381.

[ballardw · Accepted solution]

The largest number that SAS will treat as a date currently is 6589335 which corresponds to 31DEC20000 (yes 5 zeroes or rounghly 18,000 years in the future.

When you call function that expects to use numeric values with a character value like '19650214' or '20100429' then SAS applies internal rules to convert the characters to numbers (that is the conversion message you see).

So '19650214' becomes the number 19650214. Which is 13060879 larger than the largest allowable date. So the YRDIF, and YEAR, MONTH, DAY, INTNX, INTCK, HOLIDAY and any other date related function will fail.

It is your responsibility to convert character values to dates using a proper informat for the type of value you currently have.

---

@SASPreK wrote:

Thank you for the info. However, my dob variable does not seem to be empty in the dataset. Also, the log shows otherwise, see below the bold part.

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
100:15 100:20
NOTE: Invalid argument to function YRDIF(19650214,20100429) at line 100 column 9.
sample_dt=20100429 dob=19650214 age=. _ERROR_=1 _N_=1

Please see this was another note 

NOTE: Mathematical operations could not be performed at the following places. The results of the
operations have been set to missing values.
Each place is given by: (Number of times) at (Line):(Column).
7272426 at 100:9

---

[SASPreK]

Thank @ballardw @Tom @Kurt_Bremser @Sajid01  for all your very helpful comments. 

With the help of your comments and carefully looking at the error messages and variable attributes I realized my data was in this format.

data have;
input dob $ sample_dt $ ;
format dob sample_dt 8.;
informat dob sample_dt 8.;
datalines;
19650214 20100429
19800724 20210823
19991208 20090908
;

My date variables were not in date format as pointed out by @ballardw but my variables were not being converted to date variable by using any format and informat of yymmdd8. functions because it had it's own format and informat of 8. So, I checked the sas community and used the solution provided by @ballardw to this thread https://communities.sas.com/t5/SAS-Enterprise-Guide/Convert-Character-to-Date/td-p/742951?lightbox-m... However, my actual dataset was not completely clean and was still giving me some errors at specific rows and columns because the date values had some special characters or missing values. After following the two steps of converting to date and cleaning my dataset, I was finally able to get the output I had wanted. Thank you all once again 🙂

[Kurt_Bremser]

"Not working" on its own is not helpful (to be very polite).

Describe in detail what does not meet your expectations, and post the log from the code into a window opened with the </> button.