BookmarkSubscribeRSS Feed
heyyou1
Fluorite | Level 6

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. 

heyyou1_0-1642611938706.png

heyyou1_3-1642612010676.png

heyyou1_2-1642611995412.png

heyyou1_1-1642611973751.png

 

 

 

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?

3 REPLIES 3
Reeza
Super User

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. 

heyyou1_0-1642611938706.png

heyyou1_3-1642612010676.png

heyyou1_2-1642611995412.png

heyyou1_1-1642611973751.png

 

 

 

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
Super User

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
Super User

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.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 665 views
  • 0 likes
  • 4 in conversation