Char date to date format

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Char date to date format

Hi,

I am relatively new to SAS and cannot seem to perform a basic datatype conversion.

Please help.

I have a date in the format 20140407 and want to get it into the format 07APR2014.

I am using the query builder with the following:

input(var, yymmdd8.)

Then changing the informat to date9. but I get "ERROR: Invalid date value"

The strangest thing is that the resulting table populates with 07APR2014 but my filter and query step has a big cross.

Can someone explain this and am I supposed to be concerned with it?

Regards

Sunitha


Accepted Solutions
Solution
‎04-07-2014 08:40 AM
Super User
Posts: 6,972

Re: Char date to date format

Must be something with the data. I got the "Invalid date value" when I simply fed it "xxxxxxxx".

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 6,972

Re: Char date to date format

Please provide the whole data step / proc sql code.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 12

Re: Char date to date format

PROC SQL;

CREATE TABLE WORK.Query_for_R_ENDDATE AS SELECT

R_ENDDATE.VAR1,

       R_ENDDATE.VAR2,

       R_ENDDATE.VAR3,

       R_ENDDATE.VAR4,

       R_ENDDATE.VAR5,

       R_ENDDATE.VAR6,

       R_ENDDATE.ENDDATE,

       (input(R_ENDDATE.ENDDATE ,yymmdd8.)) FORMAT=DATE9.0 AS ENDDATE_CNV,

       R_ENDDATE.ENDDATE_CMP

FROM WORK.R_ENDDATE AS R_ENDDATE;

QUIT;

Super User
Super User
Posts: 7,430

Re: Char date to date format

Sorry, editing this as the location of the format makes no difference in the code.  I would guess that as the below works, there is something in your data.  Perhaps post a couple of test lines to see.

You have the format statement in a incorrect position, something like this should work.  (Note you should also be checking for missing, incorrect or partial dates).

data have;
  attrib enddate format=$8.;
  infile datalines;
  input enddate;
datalines;
20140313
20140205
;
quit;

proc sql;
  create table want as
  select  input(enddate,yymmdd8.) as enddate_cnv format=date9.
  from    work.have;
quit;

Solution
‎04-07-2014 08:40 AM
Super User
Posts: 6,972

Re: Char date to date format

Must be something with the data. I got the "Invalid date value" when I simply fed it "xxxxxxxx".

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 12

Re: Char date to date format

Thank you kindly.

I have 9896210 records, any easy way of identifying the invalid date?

Super User
Super User
Posts: 7,430

Re: Char date to date format

In interactive session you can goto data->where clause.  Or alternatively in code:

proc sql;

     create table tmp as select distinct enddate from r_endate;

quit;

Then look at the distinct values. You could do a suite of checks really, len(strip(enddate))< 8.

Another way:

proc sql;

     create table tmp  as

     select     distinct enddate,

                    input(enddate,yymmdd8.) as num_enddate

     from        r_enddate

     where calculated num_enddate is null;

quit;

Basically you are trying to see the unique values, they will normally stand out.

Super User
Posts: 6,972

Re: Char date to date format

You just have to select for enddate_cnv = . (missing value)

That will give you all the defective records. A quick glance will most probably show you where the problem is.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,099

Re: Char date to date format

It looks like ENDDATE might be a numeric variable, rather than character.  That would explain the results you are getting, and would suggest a change in the selection:

input(put(R_ENDDATE.ENDDATE,8.), yymmdd8.) as enddate_cnv format=date9.,

Without applying the PUT function, SAS is forced to perform a numeric-to-character conversion in order to apply the INPUT function.  SAS will not necessarily convert the value to the character string that you hope for.

🔒 This topic is solved and locked.

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

Discussion stats
  • 8 replies
  • 518 views
  • 3 likes
  • 4 in conversation