How to convert a date&time variable into a time variable

[machete]

Dear all

I am trying to convert a date variable of the below format:

“05/01/201208:45:24”

(informat is DATETIME21.)

Using this command:

newtime=substr(Last_Modification_Date,11,9);

as I would like to have only hh:mm:ss

however with the above command i do not receive result:

‘.’

do you have an idea why this code does not
work?

Many thanks in advance for the support

Best

Neo

[Jagadishkatam]

Please try the below code

data have;

    input date $18.;

    time=substr(date,11,8);

cards;

05/01/201208:45:24

;

Thanks,

Jagadish

[machete]

Hello Jagadish,

i am traying to ammend your code as I need to execute the code for the whole dataset, could you tell me where the mistake is in your opinion:

data match2.eurodailynew;

set match2.eurodaily;

input LastModificationDate $18.;

time=substr(LastModificationDate,11,8);

cards;

thanks

Neo

[data_null__]

TIMEPART

[machete]

i treid this one

data match2.eurodailynew;

set match2.eurodaily;

LastModificationDate=datetime();

time=timepart(LastModificationDate);

run;

but then the time variable delivers a number instead of hh:mm:ss, how can i reformat this?

Thnx

Neo

[Jagadishkatam]

Please try to use the time8. format

data eurodailynew;

set match2.eurodaily;

LastModificationDate=datetime();

time=timepart(LastModificationDate);

format time time8.;

run;

Thanks,

Jagadish

[machete]

Hi I used the code you proposed, it  just converted the original time variable to the current date today:

what was before e.g 06JAN2012:11:57:06.00

now is : 02OCT2013:17:43:02.95

there should be some mistake somewhere or?

Best

Neo

[Jagadishkatam]

Hi, could you please let me know the variable from match2.eurodaily from which you want to take only the timepart.

As an example consider that you have a variable name x with datetime data. the use the below code

data eurodailynew;

set match2.eurodaily;

LastModificationDate=input(x,datetime18.);

time=timepart(LastModificationDate);

format time time8.;

run;

Please try this and let me know if there is any problem.

Thanks,

Jagadish

[machete]

the variable is called 'lastmodificationdate', below an example record:

'06JAN2012:11:57:06.00'

it has the following format: DATETIME21.2

the code you provided does still not work

i also tried to ammend it but it still does not work:

data match2.eurodailynew;

set match2.eurodaily;

newdate=input(LastModificationDate,datetime18.);

time=timepart(newdate);

format time time8.;

run;

Neo

[Reeza]

Post the results from proc contents from your data please, and the code or log. TimePart should work, unless you have a character variable or are doing something else incorrectly.

proc contents data=match2.eurodaily;

run;

data match2.eurodailynew;

set match2.eurodaily;

newdate=input(LastModificationDate,datetime18.);

time=timepart(LastModificationDate);

time2=timepart(newdate);

format time time2 time8.;

run;

[machete]

Hi

Below thw proc contents. Your code worked and converted the time variable but the newdate variable is empty, should it be like that?

The CONTENTS Procedure

     

Data Set Name	NEO.EURODAILY	Observations	6938
Member Type	DATA	Variables	37
Engine	V9	Indexes	0
Created	02 Oct 2013 Wed 14:35:53 o'clock	Observation Length	4880
Last Modified	02 Oct 2013 Wed 14:35:53 o'clock	Deleted Observations	0
Protection		Compressed	NO
Data Set Type		Sorted	NO
Label
Data Representation	WINDOWS_64
Encoding	wlatin1 Western (Windows)

   

Data Set Page Size	16384
Number of Data Set Pages	2313
First Data Page	1
Max Obs per Page	3
Obs in First Data Page	2
Number of Data Set Repairs	0
Filename	C:\PhD thesis\SAS files\eurodaily.sas7bdat
Release Created	9.0301M0
Host Created	X64_7HOME

         

29	A	Char	244	$244.	$244.	A
19	Account	Char	92	$92.	$92.	Account
3	Amount1_Cur1	Num	8	BEST12.	BEST12.
4	Amount1_Cur2	Num	8	BEST12.	BEST12.
30	B	Char	244	$244.	$244.	B
14	Branch	Char	256	$CHAR256.	$CHAR256.
9	Broker	Char	256	$CHAR256.	$CHAR256.
27	COTSY	Char	34	$34.	$34.	COTSY
17	Comment	Char	256	$CHAR256.	$CHAR256.
28	Cpty	Char	244	$244.	$244.	Cpty
10	CptyName	Char	256	$CHAR256.	$CHAR256.
2	Cur1_Cur2	Char	256	$CHAR256.	$CHAR256.
1	Deal_Id	Num	8	BEST12.	BEST12.	Deal Id
12	FolderName	Char	256	$CHAR256.	$CHAR256.
7	LastModificationDate	Num	8	DATETIME21.2	DATETIME21.
37	Last_Modification_Date	Num	8
20	LegalEntity	Char	177	$177.	$177.	Legal Entity
21	LegalEntityCountry	Char	106	$106.	$106.	Legal Entity Country
23	LegalEntityDepartment	Char	42	$42.	$42.	Legal Entity Department
24	LegalEntityIndustrySubtype	Char	50	$50.	$50.	Legal Entity Industry Subtype
25	LegalEntityIndustryType	Char	34	$34.	$34.	Legal Entity Industry Type
22	LegalEntityRegion	Char	20	$20.	$20.	Legal Entity Region
26	Master	Char	177	$177.	$177.	Master
13	PortfolioName	Char	256	$CHAR256.	$CHAR256.
6	SpotMargin	Num	8	BEST12.	BEST12.
5	SpotRate	Num	8	BEST12.	BEST12.
15	User	Char	256	$CHAR256.	$CHAR256.
16	UserName	Char	256	$CHAR256.	$CHAR256.
8	ValueDate	Char	256	$CHAR256.	$CHAR256.
31	aggressor	Char	244	$244.	$244.	Aggressor non aggressor 1- 0
11	cokis_code	Char	256	$CHAR256.	$CHAR256.	Cokis code
18	cokis_id	Char	10	$10.	$10.	cokis_id
35	dt1	Num	8	DATETIME19.
33	new_time2	Char	8	$8.	$8.	new time2
36	newtime	Num	8
34	segment_new	Char	15	$15.	$15.	segment new
32	total__aggressor10	Char	244	$244.	$244.	total aggressor non agressor

[Vince28_Statcan · Accepted solution]

lastmodificationdate is already a datetime and thus stored as numeric. Hence, input(lastmodificationdate, datetime18.) won't work as it attempts to convert a string of the form "ddmmmyy:hh:mm:ss" into a numeric value representing the number of seconds since 01JAN1960:00:00:00 (which is what your variable already is).

Data Null had the appropriate answer, all you needed was to attribute an appropriate format to the newly created variable after using timepart.

data match2.eurodailynew;

set match2.eurodaily;

time=timepart(LastModificationDate);

format time time8.;

run;

Should do the appropriate conversion. All date, time and datetime format variables are stored internally as a numeric count. Either the number of days since 01jan1960, the number of seconds since 00:00:00.00 or the number of seconds since 01jan1960:00:00:00.00 respectively. Any new variable created from manipulation of these dates will return a numeric value with a similar count. You simply need to tell SAS that you want it to be represented as a date/time or datetime format via a format statement after creating the variable.

Vince