topic Re: convert date with time from character format to numeric date in SAS Programming

convert date with time from character format to numeric date

Amy0223 — Fri, 20 Nov 2020 20:42:47 GMT

Hi all,

I'm trying to figure out how to convert this date with time (character) to date without time( numeric). Any help is greatly appreciated!

EXENDTC 2019-01-16T14:03 --> 2019-01-16

Thank you!

Re: convert date with time from character format to numeric date

novinosrin — Fri, 20 Nov 2020 20:49:45 GMT


data test;
char_dt='2019-01-16T14:03';
num_dt=input(char_dt,yymmdd10.);
format num_dt mmddyy10.;
run;

Basically, the idea is to read the 1st 10 bytes of characters with the appropriate date informat and convert to a numeric date.

Then you could just format the numeric date value with any FORMAT you like

Re: convert date with time from character format to numeric date

Amy0223 — Fri, 20 Nov 2020 20:57:27 GMT

Thanks for your quick response. When I tried it, I got this note for format num_dt mmddyy10.;
NOTE 484-185: Format $MMDDYY was not found or could not be loaded.

Could you give some suggestions on how to resolve it? Thanks!

Re: convert date with time from character format to numeric date

novinosrin — Fri, 20 Nov 2020 21:04:28 GMT

NOTE 484-185: Format $MMDDYY was not found or could not be loaded.

Are you sure you used MMDDYY10. format or $MMDDYY?

Not sure what you wrote in the code.

Also, are you sure your datetime value is stored as character? Did you check that out using PROC CONTENTS?

Re: convert date with time from character format to numeric date

Amy0223 — Fri, 20 Nov 2020 21:09:49 GMT

Yes, EXENDTC is a character with $26. and I used MMDDYY10. but the note says $MMDDYY. I'm not sure why but thank you again for your help. I really appreciate it!

Re: convert date with time from character format to numeric date

novinosrin — Fri, 20 Nov 2020 21:19:08 GMT

Hmm,

Let's go in piecemeal-

First try without assigning a format-

char_dt='2019-01-16T14:03';
num_dt=input(char_dt,yymmdd10.);

See, what you get for num_dt. You should see some numbers(integers). These are SAS date numbers. If you do not see these numbers, you got to check your character pattern. The pattern assumes the first 10 characters of char_dt is in the form yyyy-mm-dd i.e. readable by the informat yymmdd10. I hope this should make sense to you.

char_dt='2019-01-16T14:03';
num_dt=input(char_dt,yymmdd10.);
format num_dt mmddyy10.;

With format being assigned, you should be able to see this

char_dt	num_dt
2019-01-16T14:03	01/16/2019

If the above is a success, you should the try assigning a format to the number dates for display

Re: convert date with time from character format to numeric date

novinosrin — Fri, 20 Nov 2020 21:27:38 GMT

And another alternative is, using YMDDTTM informat. However this one converts your character datetime value to a numeric datetime value, which means you would need to extract the DATEPART value from a DATETIME value using datepart function and then apply the date format of your choice.

data test;
char_dt='2019-01-16T14:03';
num_dt=datepart(input(char_dt,ymddttm24.));
format num_dt mmddyy10.;
run;
proc print noobs;run;

Re: convert date with time from character format to numeric date

ballardw — Fri, 20 Nov 2020 21:34:40 GMT

@Amy0223 wrote:

Yes, EXENDTC is a character with $26. and I used MMDDYY10. but the note says $MMDDYY. I'm not sure why but thank you again for your help. I really appreciate it!

Since you have EXENDTC with a $26 format and the length of a value like 2019-01-16T14:03 is 16 characters it may be that you have one or more leading spaces which would prevent the informat from working.

Example, the log will show the values.

data example;
   v1 = "2019-01-16T14:03          ";
   v2 = "          2019-01-16T14:03";
   length1 = length(v1);
   length2 = length(v2);
   put length1= length2=;
   d1 = input(v1,yymmdd10.);
   d2 = input(v2,yymmdd10.);
   put d1 = mmddyy10. d2 = yymmdd10.;
run;

If you do a similar length check and find some values have leading spaces you can use

input(strip(variablename), yymmdd10.)

so the leading spaces are ignored for the input function.

A not about $mmddyy means you attempted to use the format on a character variable. Which means you need to show the actual code. Copy from the log the data step and all the notes, paste into a code box opened with the </>.

You do need to create a new variable, you cannot change the type of a variable in SAS once it has been created. So if you attempted to do something like: EXENDTC = input(EXENDTC, yymmdd10) that is right out.