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

[metallon]

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.

[Loko]

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

[Astounding]

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.

[RW9]

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;

[metallon]

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;

[RW9]

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

[metallon]

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

[metallon]

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'

[Astounding]

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;

[RW9 · Accepted solution]

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. 

[metallon]

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;

[RW9]

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