Converting character variable into date variable

[Kbug]

Hello, I am using SAS 9.4 and I am trying to convert my character variable into date format.

I have a file named diagnosistest2 and I want to change the variable, ADMIT_DTTM from text to date format. ADMIT_DTTM has 10 characters. I don't quite understand how to do a conversion, but I have tried variations of this code(below) and when I run it it replaces all the values with "."    How would I go about fixing my code to make the conversion work?

data cdiff.diagnosistest3;
set cdiff.diagnosistest2;
ADMIT_DTTM2=input(ADMIT_DTTM, 10.);
run;

[novinosrin]

Can you please post a sample of the values of what your 

ADMIT_DTTM

variable look like? 

[Kbug]

Here are sample values for ADMIT_DTTM:

2017-11-06

2017-10-26

2017-09-06

2017-08-28

2017-11-06

2018-01-23

2018-02-21

[novinosrin]

Ok Try

ADMIT_DTTM2=input(ADMIT_DTTM, yymmdd10.);
format ADMIT_DTTM2 yymmdd10.;
run;

[Kbug]

There is an error. 48-59 the format $yymmdd was not found or could not be loaded.

[novinosrin]

Please show us your code

[Kbug]

data cdiff.diagnosistest3;
set cdiff.diagnosistest2;
ADMIT_DTTM2=input(ADMIT_DTTM, yymmdd10.);
format ADMIT_DTTM2 yymmdd10.;
run;

[novinosrin]

@Kbug   Here is test, please run and see where's the discrepancy

data have;
input date $10.;
cards;
2017-11-06
2017-10-26
2017-09-06
2017-08-28
2017-11-06
2018-01-23
2018-02-21
;

data want;
set have;
new_date=input(date,yymmdd10.);
format new_date yymmdd10.;
run;

proc print data=want nobs;
run;

[Kbug]

It ran successfully, I see that the date changes from 10 char to 8 yymmdd10. The code on the test looks exactly like how it is written on my editor. I don't understand why variable ADMIT_DTTM cannot load the format but the test code can.

[Tom]

---

@Kbug wrote:

It ran successfully, I see that the date changes from 10 char to 8 yymmdd10. The code on the test looks exactly like how it is written on my editor. I don't understand why variable ADMIT_DTTM cannot load the format but the test code can.

---

You cannot change the type of an existing variable.  So ADMIT_DTTM is already defined as character.  You need to create a new variable that numeric to hold the date value.  You can use rename statement or rename= option to change the name if you want.

data want;
  set have;
  my_date=input(admit_dttm,yymmdd10.);
  format my_date yymmdd10.;
  rename admit_dttm=admit_dttm_char my_date=admit_dttm;
run;

But as I said before I wouldn't call a variable that has a DATE value with a name that implies it has a DATETIME value. You will just confuse people.

[Tom]

---

@Kbug wrote:

There is an error. 48-59 the format $yymmdd was not found or could not be loaded.

---

If you try to attach a format of the wrong type to a variable SAS will silently convert it to the proper type.

So it looks like you have tried to attach the YYMMDD. format to a character variable.

[Tom]

What values does your character variable have?

Your current program is assuming that the variable contains digits only.  If you want the value to usable as a date then it also needs to represent the number of days counted from 1960.  If you are getting only missing values then the first 10 characters of your character variable do not contain a string that represents a number.  Perhaps they look like dates with some delimiter between the parts?  perhaps like '2019-07-19'?  If so then use an appropriate INFORMAT in your INPUT() statement.

Also you also mentioned that you want a DATE value, but the name of the variable makes it look like it probably contains DATETIME values instead.   DATETIME values are the number of seconds counted from 1960 instead of the number of days that a DATE value would contain.

[Kbug]

It has yyyy-mm-dd as the values. So I would use a input statement of some sort then?
Originally it was yyyy-mm-dd 00:00:00, then I trimmed the string and changed the character length as needed.

[Tom · Accepted solution]

---

@Kbug wrote:

It has yyyy-mm-dd as the values. So I would use a input statement of some sort then?
Originally it was yyyy-mm-dd 00:00:00, then I trimmed the string and changed the character length as needed.

---

If you use the YYMMDD10. informat then it will convert that into a date value.  No need to trim it first since only the first 10 characters will be used by the informat.

data want;
  set have;
  my_date = input(charvar,yymmdd10.);
  format my_date yymmdd10.;
run;

[Cerium23]

How do I convert days to months? Can someone please help.