change character date and time format into numeric

[RebeccaJW]

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!

[novinosrin]

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

[RebeccaJW]

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!

[novinosrin]

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.

[RebeccaJW]

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;

[novinosrin]

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

[RebeccaJW]

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!

[novinosrin]

@RebeccaJW 

Try this

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

[RebeccaJW]

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!

[novinosrin]

 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

[novinosrin]

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
;

[RebeccaJW]

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.

[novinosrin · Accepted solution]

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. 

[RebeccaJW]

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

[novinosrin]

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;