topic Re: Convert Character to date in SAS Programming

Convert Character to date

Eugenio211 — Fri, 11 Nov 2022 19:29:04 GMT

hello,

Can someone help with converting a character to date? I just need MMDDYY thanks a lot.

below is the sample data:

FundedDate

2022-11-02 12:32:48.4126877 -04:00

2022-10-22 18:16:32.7534728 -04:00

2022-10-12 10:45:02.4220416 -04:00

2022-10-06 14:58:36.9955507 -04:00

2022-10-04 11:01:24.8600005 -04:00

2022-10-15 18:11:57.0140644 -04:00

2022-11-02 12:32:48.4126877 -04:00

2022-10-22 18:16:32.7534728 -04:00

2022-10-12 10:45:02.4220416 -04:00

2022-10-06 14:58:36.9955507 -04:00

2022-10-04 11:01:24.8600005 -04:00

2022-10-15 18:11:57.0140644 -04:00

2022-10-19 19:18:16.7061785 -04:00

2022-11-07 11:08:37.9587636 -05:00

2022-10-21 15:58:18.4112167 -04:00

2022-10-31 16:33:16.2012582 -04:00

2022-10-23 17:18:36.6695760 -04:00

Re: Convert Character to date

Kurt_Bremser — Fri, 11 Nov 2022 19:31:34 GMT

Extract the first 10 characters (SUBSTR) and convert them to a date with the INPUT function, using the YYMMDD10. informat.

Re: Convert Character to date

PeterClemmensen — Fri, 11 Nov 2022 19:33:16 GMT

Try this

data have;
input FundedDate $ 1 - 35;
datalines;
2022-11-02 12:32:48.4126877 -04:00
2022-10-22 18:16:32.7534728 -04:00
2022-10-12 10:45:02.4220416 -04:00
2022-10-06 14:58:36.9955507 -04:00
2022-10-04 11:01:24.8600005 -04:00
2022-10-15 18:11:57.0140644 -04:00
2022-11-02 12:32:48.4126877 -04:00
2022-10-22 18:16:32.7534728 -04:00
2022-10-12 10:45:02.4220416 -04:00
2022-10-06 14:58:36.9955507 -04:00
2022-10-04 11:01:24.8600005 -04:00
2022-10-15 18:11:57.0140644 -04:00
2022-10-19 19:18:16.7061785 -04:00
2022-11-07 11:08:37.9587636 -05:00
2022-10-21 15:58:18.4112167 -04:00
2022-10-31 16:33:16.2012582 -04:00
2022-10-23 17:18:36.6695760 -04:00
;

data want;
   set have;
   dt = input(substr(FundedDate, 1, 10), yymmdd10.);
   format dt mmddyy10.;
run;

Re: Convert Character to date

Tom — Fri, 11 Nov 2022 19:43:10 GMT

Just use INPUT() function with the YYMMDD10. informat.

You can then attach any date type format you want to the new numeric variable. But I would not recommend using MMDDYY because whether you display dates in Month-Day order or Day-Month order it will confuse half of your audience. So use either YYMMDD or DATE to avoid confusion.

data want;
  set have;
  actualdate = input(fundeddate,yymmdd10.);
  format actualdate yymmdd10.;
run;

Re: Convert Character to date

Eugenio211 — Fri, 11 Nov 2022 19:49:42 GMT

Hi - I used the substr function to extract the date and format it, however i got the worng date after formatting, any idea?

example

Character Date = 2022-10-22 18:16:32.7534728 -04:00

Substr = 2022-10-22

after format = 10/02/22

here is my data step

data LOC_List1; set LOC_List;
Funded_Dt = substr(FundedDate,1,10);
FundedDt = input(Funded_Dt,anydtdte.);
format FundedDt mmddyy.;
run;

Re: Convert Character to date

Kurt_Bremser — Fri, 11 Nov 2022 19:53:23 GMT

Please reread the thread. NOBODY told you to use the ANYDTDTE informat. We all advised you to use YYMMDD10.

Re: Convert Character to date

Tom — Fri, 11 Nov 2022 21:17:30 GMT

Read the documentation on the format you used.

ANYDTDTE w.

Syntax Description

w

specifies the width of the input field.

Default	9
Range	5–32

If you don't tell it what WIDTH (how many characters to read) then it uses the DEFAULT width.

In this case the default is 9 characters. So for that value it read

2022-10-2

In general you should only use a guessing procedure, like the ANYDT... series of informats, when the format of the strings is unknown. But your provided example were all using 10 characters in YYYY-MM-DD to record the date part of the string. So use the YYMMDD10. informat .