BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
machete
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Vince28_Statcan
Quartz | Level 8

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

11 REPLIES 11
Jagadishkatam
Amethyst | Level 16

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
machete
Calcite | Level 5

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



machete
Calcite | Level 5

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
Amethyst | Level 16

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
machete
Calcite | Level 5

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
Amethyst | Level 16

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
machete
Calcite | Level 5


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
Super User

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
Calcite | Level 5

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

Vince28_Statcan
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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