Problems calculating age from a datetime

[heyyou1]

The current table I have is different people. One of the columns is their birthdate formatted as numeric, datetime16, length 8. i.e. -> 21SEP96:00:00:00

I convert this to date9. and add it as a new column

DATA have;
set have;
birth_date_formatted= input(birth_datetime_formatted,date9.);
RUN;

The result is this, an empty column. 

I then use the following code to split the dataset if the person is over 65 years old.

PROC sql;
	create table over_65_filtered as 
	select * FROM have
	WHERE intck('year',birth_date_formatted,TODAY()) >= 65;
QUIT;

However, this creates an empty table. 

Why is the datetime to date conversion resulting in empty data?

[Reeza]

Your approach is incorrect, to convert a datetime to a date, use datepart().
INPUT is used to convert a character to a date/datetime.
I do not recommend using the name HAVE repeated.

DATA want;
set have;
birth_date_formatted= datepart(USER_pat_birth_date);
format birth_date_formatted date9.;
RUN;

EDIT: to refer to correct variable.

---

@heyyou1 wrote:

The current table I have is different people. One of the columns is their birthdate formatted as numeric, datetime16, length 8. i.e. -> 21SEP96:00:00:00

I convert this to date9. and add it as a new column

DATA have;
set have;
birth_date_formatted= input(birth_datetime_formatted,date9.);
RUN;

The result is this, an empty column. 

I then use the following code to split the dataset if the person is over 65 years old.

PROC sql;
	create table over_65_filtered as 
	select * FROM have
	WHERE intck('year',birth_date_formatted,TODAY()) >= 65;
QUIT;

However, this creates an empty table. 

Why is the datetime to date conversion resulting in empty data?

---

[ballardw]

What happens when you run something like this:

DATA have;
set have;
birth_date_formatted= input(birth_datetime_formatted,date9.);
RUN;

Is that the variable BIRTH_DATETIME_FORMATTED, if it is numeric gets turned into a string of digits. If the value was a datetime like  21SEP96:00:00:00 then the digits would be 1158883200, which in no way resembles a DATE9. format, for use by the input function before the informat is attempted.

Seldom does implicit conversion of a numeric value get converted to a string that Input requires for a given numeric value. One example:

data example;
   x=123;
   y= input(x,8.);
run;

Y in this example will be missing. That is because before the informat 8. is used to read the value of X is turned into a string using the BEST12 format and is right justified. So there are nothing but spaces in the first 8 positions to read.

If you are going to attempt to use INPUT with a numeric variable it is up to you to convert the number into a string you know the content. Example

data example;
   x=123;
   y= input(put(x,best12. -L),8.);
run;

The Put function creates a string, the format controls the appearance and the option -L left justifies the result so the leading spaces don't become an issue.

It is better to use the functions such as DATEPART or MDY or DHMS or HMS to covert numeric date and time values around.

https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/... has a PDF with much information about working with dates and such.

[Kurt_Bremser]

Maxim 2: Read the Log.

You will find a NOTE for an uninitialized variable.

This will be the correct code:

data want;
set have;
birth_date_formatted = datepart(user_part_birth_date);
format birth_date_formatted yymmdd10.;
run;

Use the existing variable as source, the correct function, and apply a date format.