topic Re: Converting character variable into date variable in SAS Programming

Converting character variable into date variable

Kbug — Fri, 19 Jul 2019 15:40:33 GMT

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;

Re: Converting character variable into date variable

novinosrin — Fri, 19 Jul 2019 15:45:20 GMT

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

ADMIT_DTTM

variable look like?

Re: Converting character variable into date variable

Kbug — Fri, 19 Jul 2019 15:47:30 GMT

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

Re: Converting character variable into date variable

novinosrin — Fri, 19 Jul 2019 15:47:49 GMT

Ok Try

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

Re: Converting character variable into date variable

Tom — Fri, 19 Jul 2019 15:48:08 GMT

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.

Re: Converting character variable into date variable

Kbug — Fri, 19 Jul 2019 15:53:07 GMT

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

Re: Converting character variable into date variable

novinosrin — Fri, 19 Jul 2019 15:54:02 GMT

Please show us your code

Re: Converting character variable into date variable

Kbug — Fri, 19 Jul 2019 15:56:19 GMT

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

Re: Converting character variable into date variable

Kbug — Fri, 19 Jul 2019 15:58:34 GMT

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.

Re: Converting character variable into date variable

Tom — Fri, 19 Jul 2019 15:59:34 GMT

@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.

Re: Converting character variable into date variable

novinosrin — Fri, 19 Jul 2019 15:59:59 GMT

@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;

Re: Converting character variable into date variable

Tom — Fri, 19 Jul 2019 16:01:32 GMT

@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;

Re: Converting character variable into date variable

Kbug — Fri, 19 Jul 2019 16:05:07 GMT

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.

Re: Converting character variable into date variable

Tom — Fri, 19 Jul 2019 16:48:41 GMT

@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.

Re: Converting character variable into date variable

Cerium23 — Fri, 19 Jul 2019 23:43:21 GMT

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

Re: Converting character variable into date variable

Tom — Sat, 20 Jul 2019 00:08:44 GMT

@Cerium23 wrote:

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

Depends on what you mean.

Normally for generating statistics on number of months I like to just divide number of days by a constant like 30 than worry about the varying number of days in calendar months. To get the difference in days between two dates just subtract them.

If you want to get real difference of calendar months between two dates you can use the INTCK() function with the MONTH interval. But be careful as it counts the number of boundaries crossed. So a difference of 30 days might return 0,1 or 2 boundaries crossed depending on the actual dates being compared.

If you just want to get the month within the year from a date value use the MONTH() function.