BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
aminkarimid
Lapis Lazuli | Level 10

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.

1 ACCEPTED SOLUTION

Accepted Solutions
aminkarimid
Lapis Lazuli | Level 10
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

6 REPLIES 6
ballardw
Super User

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.

aminkarimid
Lapis Lazuli | Level 10
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. -##
Tom
Super User Tom
Super User

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);

 

Rick_SAS
SAS Super FREQ

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;
aminkarimid
Lapis Lazuli | Level 10

Hello; Thank you for your attention;

Please help me how can I round all time.

aminkarimid
Lapis Lazuli | Level 10
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;

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
  • 6 replies
  • 6426 views
  • 1 like
  • 4 in conversation