topic Re: How to convert a character variable to a numeric date type variable in SAS Programming

How to convert a character variable to a numeric date type variable

metallon — Mon, 11 Apr 2016 13:15:59 GMT

Hi SAS Experts,

I try to convert one of my character columns into a date one.

The following statement works fine but..

data H_finished;
set work.finished;
format entry_date date9.;
run;

problem :

The column type remains character. If I use the format I acutally want E8601DNw. I get the error:
"An operation that opens a SAS® data set in SAS® Enterprise Guide® fails with the error "Format names must start with an English letter" The dates are already correctly formatted yyyy-mm-dd just as a character.

Re: How to convert a character variable to a numeric date type variable

Loko — Mon, 11 Apr 2016 13:19:21 GMT

Hello,

There are only 2 type of variables within sas: character and numeric.

Take a look at input function to convert your character variable to a numeric one and afterwards you can apply whatever format you like.

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000180357.htm

Re: How to convert a character variable to a numeric date type variable

Astounding — Mon, 11 Apr 2016 13:19:46 GMT

You'll need to give some examples of what ENTRY_DATE actually contains. And if it's not 100% obvious, please provide what dates those represent.

Re: How to convert a character variable to a numeric date type variable

RW9 — Mon, 11 Apr 2016 13:17:55 GMT

You need to convert the character string to numeric then apply a format to the numeric:

data want;
  char_date="2015-01-01";
  entry_date=input(char_date,yymmdd10.);
  format entry_date date9.;
run;

Re: How to convert a character variable to a numeric date type variable

metallon — Tue, 12 Apr 2016 11:53:12 GMT

Hi RW9

A character date like '2015-10-01'
becomes ' 203'

data H_finished;
set work.finished;
entry_date=input(entry_date,E8601DNw.);
format entry_date date9.;
run;

Re: How to convert a character variable to a numeric date type variable

RW9 — Tue, 12 Apr 2016 12:09:57 GMT

Yes, you need to read the date in the format it is in."2015-10-01" is YYMMDD10 format, not e8601:

data H_finished;
  set work.finished;
  entry_date=input(entry_date,yymmdd10.);
  format entry_date date9.;
run;

See formats for examples of structure:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a001263753.htm

Re: How to convert a character variable to a numeric date type variable

metallon — Tue, 12 Apr 2016 12:36:41 GMT

YYMMDD10 has 2 digits for year
E8601DNw. has 4 digits for year, like I do e.g. 2015

Re: How to convert a character variable to a numeric date type variable

metallon — Tue, 12 Apr 2016 12:39:01 GMT

anyway. even if I use your suggested format it does not work:

data want;
char_date="2015-01-01";
entry_date=input(char_date,yymmdd10.);
format entry_date date9.;
run;

turns out to be 01JAN2015 and I need 2015-01-01.

when I use

data H_finished;
set work.finished;
entry_date=input(entry_date,yymmdd10.);
format entry_date date9.;
run;

the date still turns out as ' 203'

Re: How to convert a character variable to a numeric date type variable

Astounding — Tue, 12 Apr 2016 12:50:21 GMT

In SAS, the format that expresses a date as YYYY-MM-DD is called yymmddd10. So to express dates in that format, you could use:

data want;
char_date="2015-01-01";
entry_date=input(char_date,yymmdd10.);
format entry_date yymmddd10.;
run;

Re: How to convert a character variable to a numeric date type variable

RW9 — Tue, 12 Apr 2016 12:54:56 GMT

Sorry, you don't seem to understand formats. Lets take the code:

data H_finished;
  set work.finished;
  entry_date=input(entry_date,yymmdd10.);
  format entry_date date9.;
run;

What this does is create a numeric variable called entry_date (all dates in SAS are numeric - a number of days since a certain timepoint) (This may be where problem arises, if your entry_date is character, you wouldnt put the numeric back into it, it needs to go into a new numeric field). To read text in as a date value, I use the input() function, this takes two parameters - the text, and the informat to read that text as. YYMMDD10. is a format that reads data as: year as either 2 or 4 length, month as 2, and day as 2. As I specify the length of the informat as 10, this will read the data as YYYY-MM-DD, which you will see is 10 characters.

Entry date will then contain the number of days since a base point, 01jan1966 or something like that, so you will see an odd number if you do not format that number.

Now the secon line, the format entry_date date9. tells SAS to display this number of days, in the format provided, in the above case this will be displayed using the date format, with 9 characters, eg. DDMONYYYY.

See this example:

data H_finished;
 entry_date="2015-10-02";
 new_date=input(entry_date,yymmdd10.);
 yymmdd_date=new_date;
 format new_date date9.;
 format yymmdd_date yymmdd10.;
run;

Run it as is - and you will see the various variables. Note that you wouldn't want to put a numeric back into character.

Re: How to convert a character variable to a numeric date type variable

metallon — Wed, 13 Apr 2016 11:56:54 GMT

Hi,

I tried to do only this small step and receive the warning

"NOTE 485-185: Informat YYMMDDD was not found or could not be loaded."

The column entry_date_new is empty.

62
63         data H_finished_new;
64            set work.H_finished;
65
66            entry_date_new=input(entry_date,yymmddd10.);
                                                                          __________
                                                                          485
NOTE 485-185: Informat YYMMDDD was not found or could not be loaded.

67
68         run;

Re: How to convert a character variable to a numeric date type variable

RW9 — Wed, 13 Apr 2016 12:58:41 GMT

Correct the typo in the format and it will work. The format is YYMMDD, and the length is 10, thus: YYMMDD10.