DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 133
Accepted Solution

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

[ Edited ]

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.


Accepted Solutions
Solution
‎04-13-2016 08:06 AM
Super User
Super User
Posts: 7,942

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

[ Edited ]

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. 

View solution in original post


All Replies
Super Contributor
Posts: 308

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

[ Edited ]

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

 

Super User
Posts: 5,497

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

[ Edited ]

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.

Super User
Super User
Posts: 7,942

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

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;
Frequent Contributor
Posts: 133

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

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;
Super User
Super User
Posts: 7,942

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

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

Frequent Contributor
Posts: 133

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

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

Frequent Contributor
Posts: 133

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

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'
Super User
Posts: 5,497

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

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;

Solution
‎04-13-2016 08:06 AM
Super User
Super User
Posts: 7,942

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

[ Edited ]

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. 

Frequent Contributor
Posts: 133

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

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;

Super User
Super User
Posts: 7,942

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

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 473 views
  • 4 likes
  • 4 in conversation