DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 57
Accepted Solution

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

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


Accepted Solutions
Solution
‎10-03-2013 07:46 AM
Super Contributor
Posts: 339

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

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

View solution in original post


All Replies
Trusted Advisor
Posts: 1,137

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

Please try the below code

data have;

    input date $18.;

    time=substr(date,11,8);

cards;

05/01/201208:45:24

;

Thanks,

Jagadish

Thanks,
Jag
Contributor
Posts: 57

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

Posted in reply to Jagadishkatam

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



Respected Advisor
Posts: 3,799

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

Contributor
Posts: 57

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

Posted in reply to data_null__

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

Trusted Advisor
Posts: 1,137

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

Please try to use the time8. format

data eurodailynew;

set match2.eurodaily;

LastModificationDate=datetime();

time=timepart(LastModificationDate);

format time time8.;

run;

Thanks,

Jagadish

Thanks,
Jag
Contributor
Posts: 57

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

Posted in reply to Jagadishkatam

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

Trusted Advisor
Posts: 1,137

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

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

Thanks,
Jag
Contributor
Posts: 57

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

Posted in reply to Jagadishkatam


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

Super User
Posts: 19,850

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

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;

Contributor
Posts: 57

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

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 NameNEO.EURODAILYObservations6938
Member TypeDATAVariables37
EngineV9Indexes0
Created02 Oct 2013 Wed 14:35:53 o'clockObservation Length4880
Last Modified02 Oct 2013 Wed 14:35:53 o'clockDeleted Observations0
Protection CompressedNO
Data Set Type SortedNO
Label
Data RepresentationWINDOWS_64
Encodingwlatin1 Western (Windows)

   
Data Set Page Size16384
Number of Data Set Pages2313
First Data Page1
Max Obs per Page3
Obs in First Data Page2
Number of Data Set Repairs0
FilenameC:\PhD thesis\SAS files\eurodaily.sas7bdat
Release Created9.0301M0
Host CreatedX64_7HOME

         
29AChar244$244.$244.A
19AccountChar92$92.$92.Account
3Amount1_Cur1Num8BEST12.BEST12.
4Amount1_Cur2Num8BEST12.BEST12.
30BChar244$244.$244.B
14BranchChar256$CHAR256.$CHAR256.
9BrokerChar256$CHAR256.$CHAR256.
27COTSYChar34$34.$34.COTSY
17CommentChar256$CHAR256.$CHAR256.
28CptyChar244$244.$244.Cpty
10CptyNameChar256$CHAR256.$CHAR256.
2Cur1_Cur2Char256$CHAR256.$CHAR256.
1Deal_IdNum8BEST12.BEST12.Deal Id
12FolderNameChar256$CHAR256.$CHAR256.
7LastModificationDateNum8DATETIME21.2DATETIME21.
37Last_Modification_DateNum8
20LegalEntityChar177$177.$177.Legal Entity
21LegalEntityCountryChar106$106.$106.Legal Entity Country
23LegalEntityDepartmentChar42$42.$42.Legal Entity Department
24LegalEntityIndustrySubtypeChar50$50.$50.Legal Entity Industry Subtype
25LegalEntityIndustryTypeChar34$34.$34.Legal Entity Industry Type
22LegalEntityRegionChar20$20.$20.Legal Entity Region
26MasterChar177$177.$177.Master
13PortfolioNameChar256$CHAR256.$CHAR256.
6SpotMarginNum8BEST12.BEST12.
5SpotRateNum8BEST12.BEST12.
15UserChar256$CHAR256.$CHAR256.
16UserNameChar256$CHAR256.$CHAR256.
8ValueDateChar256$CHAR256.$CHAR256.
31aggressorChar244$244.$244.Aggressor non aggressor 1- 0
11cokis_codeChar256$CHAR256.$CHAR256.Cokis code
18cokis_idChar10$10.$10.cokis_id
35dt1Num8DATETIME19.
33new_time2Char8$8.$8.new time2
36newtimeNum8
34segment_newChar15$15.$15.segment new
32total__aggressor10Char244$244.$244.total aggressor non agressor

Solution
‎10-03-2013 07:46 AM
Super Contributor
Posts: 339

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

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 1093 views
  • 3 likes
  • 5 in conversation