BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
metallon
Pyrite | Level 9

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.

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

11 REPLIES 11
Loko
Barite | Level 11

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
PROC Star

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Pyrite | Level 9
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
Diamond | Level 26 RW9
Diamond | Level 26

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
Pyrite | Level 9

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

metallon
Pyrite | Level 9
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
PROC Star

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Pyrite | Level 9

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
Diamond | Level 26 RW9
Diamond | Level 26

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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