How to use "where statement" for bounding time?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 107
Accepted Solution

How to use "where statement" for bounding time?

 Hello everybody,

I want to restrict time variable of my dataset and I use code which is shown below, however, it doesn't work and out of range data still exist in the dataset!

 

data sampledata04;
	set sampledata03;
	where hour(TRD_EVENT_TM) between 9 and 12;
run;

 

Attributes of dataset are:

 

Alphabetic List of Variables and Attributes
#VariableTypeLenFormatInformatLabel
4TRD_EVENT_DTNum8DATE9.DATETIME19.TRD_EVENT_DT
5TRD_EVENT_TMNum8TIME5.  
2TRD_PRNum8  TRD_PR
1TRD_STCK_CDChar15$15.00$15.00TRD_STCK_CD
3TRD_TUROVRNum81414TRD_TUROVR

 

 

 What is the problem?

 

Thanks in advance.


Accepted Solutions
Solution
3 weeks ago
Super User
Super User
Posts: 6,499

Re: How to use "where statement" for bounding time?

Use time literals.
Where MYVAR between '09:00't and '12:00't

View solution in original post


All Replies
Super User
Super User
Posts: 7,401

Re: How to use "where statement" for bounding time?

Its been mentioned before, post test data in the form of a datastep in the body of the post.

 

What I can tell from that trd_event_tm appears to be a numeric time variable, so the hour function will work.  However I cannot see any data to work out if there is data between 9 and 12 which is likely the cause as:

data test;
  time="09:11"t; output;
  time="13:10"t; output;
run;
data want;
  set test;
  where hour(time) between 9 and 12;
run;

Works fine and only returns one row. 

SAS Employee
Posts: 5

Re: How to use "where statement" for bounding time?

Could you add the dataset sample03 and sample04(or an extract from it) to your post in text? This seems OK to me.
Frequent Contributor
Posts: 107

Re: How to use "where statement" for bounding time?

Here is a sample of my dataset. Thanks.

Super User
Posts: 6,936

Re: How to use "where statement" for bounding time?


aminkarimid wrote:

Here is a sample of my dataset. Thanks.


This not a dataset, but an Excel file.

Excel spreadsheet files cannot convey datatypes and SAS formats, and are blocked in many organizations from download for security reasons.

Please use the macro provided in https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to convert your dataset to a data step and post that here.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 107

Re: How to use "where statement" for bounding time?

[ Edited ]

Hello @KurtBremser

How can I use your codes using sas 9.4 TS Level 1M3 X64_8PRO platform?

I use the code which is shown below. However, it doesn't work.

%data2datastep(sampledata03,work,test,20)

Thanks.

Super User
Posts: 6,936

Re: How to use "where statement" for bounding time?


aminkarimid wrote:

Hello @KurtBremser

How can I use your codes in sas 9.4 TS Level 1M3 X64_8PRO platform.

I use the code which is shown below. However, it doesn't work.

%data2datastep(sampledata03,work,test,20)

Thanks.


- download the .zip

- extract the .sas file to a suitable location

- open the .sas file in Enterprise Guide, or open in a text editor and copy/paste to SAS Studio

- run the code once

- you can now use the macro in the current session

 

If you have a server installation, save the code to the server, so you can %include it any time you need the macro.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 107

Re: How to use "where statement" for bounding time?

[ Edited ]

Here is a sample of my dataset.
Thanks in advance.

data WORK.SAMPLEDATA03;
  infile datalines dsd truncover;
  input TRD_STCK_CD:$15. TRD_PR:32. TRD_TUROVR:14. TRD_EVENT_DT:DATE9. TRD_EVENT_TM:TIME5.;
  format TRD_TUROVR 14. TRD_EVENT_DT DATE9. TRD_EVENT_TM TIME5.;
  label TRD_STCK_CD="TRD_STCK_CD" TRD_PR="TRD_PR" TRD_TUROVR="TRD_TUROVR" TRD_EVENT_DT="TRD_EVENT_DT";
datalines4;
IKCQ1,1,100,24MAR2008,12:19
ALBZ1,1537,10000,24MAR2008,12:28
ALBZ1,1567,10,24MAR2008,13:13
AZAB1,683,10000,24MAR2008,12:20
AZAB1,695,10,24MAR2008,13:13
BALI1,850,9260,24MAR2008,9:14
BALI1,850,2000,24MAR2008,9:15
BALI1,850,10000,24MAR2008,9:15
BALI1,850,6000,24MAR2008,9:15
BALI1,850,10000,24MAR2008,9:29
BALI1,850,10000,24MAR2008,12:28
BALI1,850,10000,24MAR2008,12:28
BALI1,865,10,24MAR2008,13:13
BANK1,1164,10729,24MAR2008,9:38
BANK1,1148,2000,24MAR2008,11:24
BANK1,1147,1575,24MAR2008,11:24
BANK1,1147,5000,24MAR2008,12:10
BANK1,1147,3425,24MAR2008,12:10
BANK1,1141,41575,24MAR2008,12:14
BANK1,1141,8425,24MAR2008,12:14
;;;;

Code inserted by @KurtBremser from the .zip

Attachment
Super User
Posts: 6,936

Re: How to use "where statement" for bounding time?

So I ran this code against your example data

data sampledata04;
	set sampledata03;
	where hour(TRD_EVENT_TM) between 9 and 12;
run;

proc print data=sampledata04 noobs;
run;

and got this result:

 TRD_                                       TRD_      TRD_
STCK_CD    TRD_PR        TRD_TUROVR     EVENT_DT    EVENT_TM

 IKCQ1         1                100    24MAR2008     12:19  
 ALBZ1      1537              10000    24MAR2008     12:28  
 AZAB1       683              10000    24MAR2008     12:20  
 BALI1       850               9260    24MAR2008      9:14  
 BALI1       850               2000    24MAR2008      9:15  
 BALI1       850              10000    24MAR2008      9:15  
 BALI1       850               6000    24MAR2008      9:15  
 BALI1       850              10000    24MAR2008      9:29  
 BALI1       850              10000    24MAR2008     12:28  
 BALI1       850              10000    24MAR2008     12:28  
 BANK1      1164              10729    24MAR2008      9:38  
 BANK1      1148               2000    24MAR2008     11:24  
 BANK1      1147               1575    24MAR2008     11:24  
 BANK1      1147               5000    24MAR2008     12:10  
 BANK1      1147               3425    24MAR2008     12:10  
 BANK1      1141              41575    24MAR2008     12:14  
 BANK1      1141               8425    24MAR2008     12:14  

Which of these observations should not be included in the result?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 107

Re: How to use "where statement" for bounding time?

I want to bind TRD_EVENT_TM as the time variable between 9:00 till 12:00. So, all observations which are not in this range must be omitted from the dataset.
Frequent Contributor
Posts: 107

Re: How to use "where statement" for bounding time?

I want to bind TRD_EVENT_TM as the time variable between 9:00 till 12:00. So, all observations which are not in this range must be omitted from the dataset.
Thanks.
SAS Employee
Posts: 5

Re: How to use "where statement" for bounding time?

Then your code was fine, but replace the "12" with "11" so it will include all the timestamps up to 11:59:59
Frequent Contributor
Posts: 107

Re: How to use "where statement" for bounding time?

What is difference between "Where MYVAR between '09:00't and '12:00't" and What you said. Because, I have different results from them.
Thanks
Super User
Posts: 6,936

Re: How to use "where statement" for bounding time?


aminkarimid wrote:
What is difference between "Where MYVAR between '09:00't and '12:00't" and What you said. Because, I have different results from them.
Thanks

There's been a lot of code suggestions in this thread. Please post the two versions that give you different results.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 10,497

Re: How to use "where statement" for bounding time?


aminkarimid wrote:
What is difference between "Where MYVAR between '09:00't and '12:00't" and What you said. Because, I have different results from them.
Thanks

HOUR(time) = 12 is true for 12:01, 12:02, 12:03 ... 12:59

between '09:00't and '12:00't will EXCLUDE minutes past 12:00 

☑ This topic is SOLVED.

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

Discussion stats
  • 18 replies
  • 418 views
  • 5 likes
  • 6 in conversation