CODEWITHSUNDEEP
datesas
boot-scootin
boot-scootin

Reputation: 12515

Convert character date to formatted date

I have the following dataset, where the values in date are of the general format <character d>mmyy:

DATA df;
INPUT date $;
DATALINES;
d0101
d0102
d0103
d0104
;
RUN;

I want each row in df to turn into the following (numeric/date type):

01JAN2018
02JAN2018
03JAN2018
04JAN2018

Here's my attempt:

DATA df_new;
    SET df;

    FORMAT date DATE9.;

    date = INPUT(SUBSTR(date, 2, 2) || PUT(INPUT(SUBSTR(date, 4, 2), 8.), MONNAME3.) || PUT(YEAR(TODAY()), 4.), DATE9.);
RUN;

However, this just gives me the following:

21185
21185
21185
21185

How can I complete this conversion?

Upvotes: 0

Views: 997

Answers (3)

Richard
Richard

Reputation: 27498

The transformation is identical to the one I answered in your other question.

There is no need to extract the numeric aspects within a string with a presumed construct d<mm><dd> for feeding into MDY. Instead, flesh out the string representation of a date, and use input with a date informat to establish the SAS date value (which is simply a number representing the number of days since the SAS date epoch, which is 01JAN1960). SAS date values are rendered according to a format. If left unformatted, you will see only an integer. Formatting the variable as date9. will cause output to render the appropriate <dd><mon><yyyy>, or format yymmdd10. which will render as <yyyy>-<mm>-<dd>

date_value = input (cats(year(today()), substr(date,2)), yymmdd10.);
format date_value date9.;

or more explicitly

year_string = cats(year(today());
mmdd_string = substr(date,2);
yyyymmdd_string = cats(year_string,mmdd_string);
date_value = input(yyyymmdd_string, yymmdd10.);
format date_value date9.;

If you absolutely need to (unlikely) store the date, rendered as a string, back in the original date variable, you would have to use put to force the rendering at value assignment time.

date = put(date_value, date9.);

Upvotes: 1

Kiran
Kiran

Reputation: 3315

something like below using mdy function and applying date9. format once you create date from mdy function, which can take month day and year to make a date 

DATA df;
INPUT date $;
DATALINES;
 d0101
 d0102
d0103
d0104
;
RUN;

data want;
  set df;
   new_date=mdy(input(substr(date,2,2),2.), input(substr(date,4,2),2.), year(today()));
  format new_date date9.;
run;

you can also use 

 new_date=mdy(substr(date,2,2), substr(date,4,2), year(today()));

but it will have following message in your log

  NOTE: Character values have been converted to numeric values at the places 
  given by: (Line):(Column).

Upvotes: 2

Dmitry.Kolosov
Dmitry.Kolosov

Reputation: 635

You are applying a format, but the date variable still contains numeric value (number of days since SAS Epoch). You are also incorrectly reusing the date variable. In the df dataset it is created as character, in df_new you are writing a numeric value to it. So to fix it, change date to dateN in the df_new dataset. And finally to convert it to character, you need to perform one more step:

...
dateN = input(substr(date, 2, 2) || put(input(substr(date, 4, 2), 8.), monName3.) || put(year(today()), 4.), date9.);
length dateC $9;
dateC = put(dateN, date9.)
...

Upvotes: 2

Related Questions