How can I round time using SAS?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 122
Accepted Solution

How can I round time using SAS?

[ Edited ]

Hello everybody

I have a little problem and appreciate if anyone could help me.

I have intraday data (Trading data of each second):

Sample of my data:

 

TRD_EVENT_DTTRD_EVENT_TMTRD_EVENT_MSTRD_STCK_IDTRD_STCK_CDTRD_INSTR_NAMETRD_STCK_GRP_ID_CDTRD_PRTRD_TUROVRTRD_TRA_NR
3/24/200812:19:35 IRB5IKCO8751IKCQ1187191011N21100122197901
3/24/200812:28:01 IRO1ALBZ0001ALBZ1والبر1N1153710000122198037
3/24/200813:13:44 IRO1ALBZ0001ALBZ1والبر1N11567109999198173
3/24/200812:20:38 IRO1AZAB0001AZAB1فاذر1N268310000122197911
3/24/200813:13:44 IRO1AZAB0001AZAB1فاذر1N2695109999198167
3/24/200809:14:57 IRO1BALI0001BALI1وبوعلي1N28509260122197242
3/24/200809:15:06 IRO1BALI0001BALI1وبوعلي1N28502000122197245
3/24/200809:15:14 IRO1BALI0001BALI1وبوعلي1N285010000122197247
3/24/200809:15:24 IRO1BALI0001BALI1وبوعلي1N28506000122197249
3/24/200809:29:27 IRO1BALI0001BALI1وبوعلي1N285010000122197296
3/24/200812:28:00 IRO1BALI0001BALI1وبوعلي1N285010000122198036
3/24/200812:28:07 IRO1BALI0001BALI1وبوعلي1N285010000122198041
3/24/200813:13:44 IRO1BALI0001BALI1وبوعلي1N2865109999198133

Explanation of headers of some columns:

[TRD_EVENT_DT] Month / Day / Year
,[TRD_EVENT_TM] Time: hh:mm:ss
,[TRD_EVENT_MS] Millisecond
,[TRD_STCK_ID] 
,[TRD_STCK_CD] 
,[TRD_INSTR_NAME] 
,[TRD_STCK_GRP_ID_CD] 
,[TRD_PR] Stock Price
,[TRD_TUROVR] Turnover
,[TRD_TRA_NR] 

 

 

I want to categorize this data in half an hour periods:

First half an hour: 9:00 << Volume_1 < 9:30

Second half an hour: 9:30 << Volume_2 < 10:00

Third half an hour: 10:00 << Volume_3 < 10:30

Fourth half an hour: 10:30 << Volume_4 < 11

Fifth half an hour: 11:00 << Volume_5 < 11:30

Sixth half an hour: 11:30 << Volume_6 << 12

 

What I'm trying to do is basically round the time part to the nearest 30 minute. 

My question is how can I do rounding data using SAS.

 

 

Thanks for your attention.


Accepted Solutions
Solution
‎05-14-2017 03:28 AM
Frequent Contributor
Posts: 122

Re: How can I round time using SAS?

Thank you; Finally I find a solution!

*Importing Data;
PROC IMPORT DATAFILE= 'D:\In process\Thesis\Intraday Pattern of Volatility\SampleData\Table87.xls'
OUT= SampleData87
DBMS= XLS;
RUN;

PROC CONTENTS DATA= Sampledata87;
RUN;
*Step1: Rounding Time;
DATA sampledata;
SET sampledata87;
TRD_EVENT_TIME = INPUT(TRD_EVENT_TM,time16.);
TRD_EVENT_ROUNDED = round(TRD_EVENT_TIME,'00:30't);
TRD_EVENT_ROUFOR=put(TRD_EVENT_ROUNDED,hhmm.);

PROC PRINT; RUN;

View solution in original post


All Replies
Super User
Posts: 11,134

Re: How can I round time using SAS?

First get a datetime variable.

It would help if your example data actually included some millisecond values as we do not know if you have values like:

 

1

.001

or something else.

 

data example;
   informat TRD_EVENT_DT mmddyy10. TRD_EVENT_TM time8. TRD_EVENT_MS best5. ;
   format TRD_EVENT_DT mmddyy10. TRD_EVENT_TM time12.3;
   input TRD_EVENT_DT TRD_EVENT_TM TRD_EVENT_MS ;
   /* assumes milliseconds are integers where 1= 1ms
      if the values are 0.001 then the division by 1000 is not needed*/
   TRD_EVENT_TM = sum(TRD_EVENT_TM , TRD_EVENT_MS/1000);
   TRD_dttme= dhms(TRD_EVENT_DT,0,0,TRD_EVENT_TM);
   format TRD_dttme TRD_dttme_rnd datetime20.3  ;
   TRD_dttme_rnd = round(TRD_dttme,1800);
   
datalines;
3/24/2008 12:19:35   25
3/24/2008 12:28:01   .
3/24/2008 13:13:44   .
3/24/2008 12:20:38   .
3/24/2008 13:13:44   .
3/24/2008 09:14:57   1
3/24/2008 09:15:06   .
3/24/2008 09:15:14   .
3/24/2008 09:15:24   .
3/24/2008 09:29:27   100
3/24/2008 12:28:00   .
3/24/2008 12:28:07   .
3/24/2008 13:13:44   .
;
run;

If your existing variables are not date and time types then you need to covert.

 

Also post data in the form of a data step. This is about the 6th topic with the same data and we still cannot tell if your data is character or SAS date and time valued.

Frequent Contributor
Posts: 122

Re: How can I round time using SAS?

The SAS System
The CONTENTS Procedure

Data Set Name WORK.SAMPLEDATA87 Observations 100
Member Type DATA Variables 10
Engine V9 Indexes 0
Created 05/13/2017 01:10:26 Observation Length 144
Last Modified 05/13/2017 01:10:26 Deleted Observations 0
Protection Compressed NO
Data Set Type Sorted NO
Label
Data Representation WINDOWS_64
Encoding wlatin1 Western (Windows)

Engine/Host Dependent Information
Data Set Page Size 65536
Number of Data Set Pages 1
First Data Page 1
Max Obs per Page 454
Obs in First Data Page 100
Number of Data Set Repairs 0
ExtendObsCounter YES
Filename C:\Users\amin\AppData\Local\Temp\SAS Temporary
Files\_TD16616_DESKTOP-623A1UK_\sampledata87.sas7bdat
Release Created 9.0401M3
Host Created X64_8PRO

Alphabetic List of Variables and Attributes
# Variable Type Len Format Informat Label
1 TRD_EVENT_DT Num 8 MMDDYY10. TRD_EVENT_DT
3 TRD_EVENT_MS Char 15 $15. $15. TRD_EVENT_MS
2 TRD_EVENT_TM Char 16 $16. $16. TRD_EVENT_TM
6 TRD_INSTR_NAME Char 15 $15. $15. TRD_INSTR_NAME
8 TRD_PR Char 15 $15. $15. TRD_PR
5 TRD_STCK_CD Char 16 $16. $16. TRD_STCK_CD
7 TRD_STCK_GRP_ID_CD Char 16 $16. $16. TRD_STCK_GRP_ID_CD
4 TRD_STCK_ID Char 15 $15. $15. TRD_STCK_ID
10 TRD_TRA_NR Char 13 $13. $13. TRD_TRA_NR
9 TRD_TUROVR Char 14 $14. $14. TRD_TUROVR

Thank you;
I need help how can I round all data time.
What is datalines statement & I want to round all time.
##- Please type your reply above this line. Simple formatting, no
attachments. -##
Super User
Super User
Posts: 6,845

Re: How can I round time using SAS?

Your sample data does not have any time variables.

Are some of those character variables supposed to be time variables? 

You will need to convert them first.

/* TRD_EVENT_TM Char 16 */
trd_event_time = input(trd_event_tm,time16.);
trd_event_30min = round(trd_event_time,'00:30't);

 

SAS Super FREQ
Posts: 3,630

Re: How can I round time using SAS?

SAS time values are measured in seconds since midnight. There are 1800 seconds in 30 minutes, so round to the nearest 1800. You can then use the rounded value in a BY statement or CLASS statement to aggregate values that occur in each half-hour period:

 

data A;
informat t time10.;
format t r time14.;
input t;
r = round(t, 1800);
datalines;
00:14:12
00:16:12
09:01:23
09:46:32
15:59:45
;
proc print; run;
Frequent Contributor
Posts: 122

Re: How can I round time using SAS?

Hello; Thank you for your attention;

Please help me how can I round all time.

Solution
‎05-14-2017 03:28 AM
Frequent Contributor
Posts: 122

Re: How can I round time using SAS?

Thank you; Finally I find a solution!

*Importing Data;
PROC IMPORT DATAFILE= 'D:\In process\Thesis\Intraday Pattern of Volatility\SampleData\Table87.xls'
OUT= SampleData87
DBMS= XLS;
RUN;

PROC CONTENTS DATA= Sampledata87;
RUN;
*Step1: Rounding Time;
DATA sampledata;
SET sampledata87;
TRD_EVENT_TIME = INPUT(TRD_EVENT_TM,time16.);
TRD_EVENT_ROUNDED = round(TRD_EVENT_TIME,'00:30't);
TRD_EVENT_ROUFOR=put(TRD_EVENT_ROUNDED,hhmm.);

PROC PRINT; RUN;
☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 191 views
  • 1 like
  • 4 in conversation