04-07-2014 07:51 AM
I am relatively new to SAS and cannot seem to perform a basic datatype conversion.
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:
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?
04-07-2014 08:40 AM
04-07-2014 07:58 AM
04-07-2014 08:14 AM
CREATE TABLE WORK.Query_for_R_ENDDATE AS SELECT
(input(R_ENDDATE.ENDDATE ,yymmdd8.)) FORMAT=DATE9.0 AS ENDDATE_CNV,
FROM WORK.R_ENDDATE AS R_ENDDATE;
04-07-2014 08:25 AM
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).
attrib enddate format=$8.;
create table want as
select input(enddate,yymmdd8.) as enddate_cnv format=date9.
04-07-2014 08:40 AM
04-07-2014 08:53 AM
In interactive session you can goto data->where clause. Or alternatively in code:
create table tmp as select distinct enddate from r_endate;
Then look at the distinct values. You could do a suite of checks really, len(strip(enddate))< 8.
create table tmp as
select distinct enddate,
input(enddate,yymmdd8.) as num_enddate
where calculated num_enddate is null;
Basically you are trying to see the unique values, they will normally stand out.
04-07-2014 09:24 AM
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.
04-07-2014 08:54 AM
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.
Need further help from the community? Please ask a new question.