topic Re: change character date and time format into numeric in New SAS User

change character date and time format into numeric

RebeccaJW — Mon, 11 Mar 2019 16:52:52 GMT

I am given a csv file including a character DateTime variable which is of the form 2019-01-19 16:26. I need to change it to a numeric datetime variable which is of the form

1863534360

This is what I have written

data Jan;
	infile "&path." dsd;
	input DateTime;
	datetime = DATETIME(DateTime);
run;

Could any one help me find the way to do this? Thank you!

Re: change character date and time format into numeric

novinosrin — Mon, 11 Mar 2019 16:55:52 GMT

data w;
char_datetime='2019-01-19 16:26';
num_datetime=input(char_datetime,anydtdtm21.);
*format num_datetime datetime20.;
run;

Re: change character date and time format into numeric

novinosrin — Mon, 11 Mar 2019 17:02:33 GMT

A safer controlled method:


data controlled_safe_method;
char_datetime='2019-01-19 16:26';
num_date=input(char_datetime,yymmdd10.);
num_time=input(scan(char_datetime,-1,' '),time5.);
num_datetime=dhms(num_date,0,0,0)+num_time;
*format num_datetime datetime20.;
keep char_datetime num_datetime;
run;

Re: change character date and time format into numeric

RebeccaJW — Tue, 12 Mar 2019 14:29:34 GMT

Thank you novinosrin! I tried your method. This is what I wrote:

data Jan;
	infile "&path." dsd;
	input DateTime;
	datetime = input(DateTime, anydtdtm21.);
	keep datetime;
run;

However, there is no values in the DateTime variable. And there is no datetime variable. Could you take a look at it? Thank you so much for your help!

Re: change character date and time format into numeric

novinosrin — Tue, 12 Mar 2019 14:42:38 GMT

Hi @RebeccaJW

In Your code

data Jan;
	infile "&path." dsd;
	input DateTime;
	datetime = input(DateTime, anydtdtm21.);
	keep datetime;
run;

The input statement statement tries to reads your datetime value as a number as you haven't instructed how the input statement should read the value, i.e either character or numeric. Since your values have characters, you need special instructions to read date/datetime values known as informats.

If you want to read as character and then convert to numeric , your input statement should be like

input datetime : $21. ;

Alternatively, If you want to read as number directly, your input statement should be like

input datetime : anydtdtm21.;

Try one of the two and let me know.

Re: change character date and time format into numeric

RebeccaJW — Tue, 12 Mar 2019 15:01:30 GMT

Thank you for replying! This is what I have tried. I kind of get the idea, but the result is still not correct... It turns out to be the first column of the .csv file, which is not DateTime. And the DateTime is still characteristic. Is this the problem with the informat statement?

data Jan;
	infile "&path." dsd;
	input DateTime :$21.;
	informat DateTime :ANYDTDTM21. ;
run;

Re: change character date and time format into numeric

novinosrin — Tue, 12 Mar 2019 15:22:19 GMT

Can you post a 2 or 3 records of your csv file if that's not violating any data security policy or atleast a mock data?

I can help you with the code.

Post your expected output with that

Re: change character date and time format into numeric

RebeccaJW — Tue, 12 Mar 2019 15:28:03 GMT

Sure! The first one is the original data. The second one is the expected output.

DateTime and Speed are changed from character to numeric. And plateNumber is changed from numeric to character.

Thank you so much for your patience!

Re: change character date and time format into numeric

novinosrin — Tue, 12 Mar 2019 15:33:30 GMT

@RebeccaJW

Try this

data Jan;
	infile "&path." dsd truncover;
	input make :$10. model  : $30. year DateTime :ANYDTDTM21. speed platenumber :$12.;
run;

Re: change character date and time format into numeric

RebeccaJW — Tue, 12 Mar 2019 18:28:58 GMT

Thank you for answering! It works!

May I ask a short question about how to change the speed from 1+ to +1 and 6- to -6 like this? Right now, my thought is to use value in proc format. I am wondering if there is any other more efficient methods.

Thank you again for your help!

Re: change character date and time format into numeric

novinosrin — Tue, 12 Mar 2019 18:36:49 GMT

how to change the speed from 1+ to +1 and 6- to -6 like this?

Do you want that a numeric variable or char variable?

I would assume it's easy to keep speed as numeric variable from the very meaning of speed however if for any reason your context has some kind of an indicator with discrete values, then you could have them formatted. So basically, that's where domain knowledge matters

Re: change character date and time format into numeric

novinosrin — Tue, 12 Mar 2019 18:56:51 GMT

If you just want to reverse position + and - from suffix to prefix

You could use this


data w;
input char_num $;
length want $8;
want=cats(compress(char_num,,'d'),compress(char_num,,'kd'));
cards;
6+
6-
0
;

Re: change character date and time format into numeric

RebeccaJW — Tue, 12 Mar 2019 19:05:14 GMT

Thank you for your reply! I also need to change the speed from character to numeric. So I did this:

proc format;
	value $Speed '12-' = -12
		     '11+' = +11;
run;

data Jan;
	infile "&path." dsd truncover firstobs=2;
	input make :$10. model  : $30. year DateTime :ANYDTDTM21. speed $ plateNumber :$10.;
	format speed Speed.;
	informat speed = input(speed, BEST.);
run;

But there are errors when I want to change speed from character to numeric.

Re: change character date and time format into numeric

novinosrin — Tue, 12 Mar 2019 19:12:33 GMT

Hi @RebeccaJW Now you are talking lol

Your requirement-"I want to change speed from character to numeric."

Try using trailsgn8. informat, straight forward and simple

data w;
input num_speed trailsgn8.;
cards;
6+
6-
0
;

Apply the above example in your original code. No need for formats or any sort of character manipulation.

I hope that helps.

Re: change character date and time format into numeric

RebeccaJW — Tue, 12 Mar 2019 19:16:34 GMT

Thank you @novinosrin! I will try it! I really appreciate your help!