How to use "where statement" for bounding time?

[aminkarimid]

 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
#	Variable	Type	Len	Format	Informat	Label
4	TRD_EVENT_DT	Num	8	DATE9.	DATETIME19.	TRD_EVENT_DT
5	TRD_EVENT_TM	Num	8	TIME5.
2	TRD_PR	Num	8			TRD_PR
1	TRD_STCK_CD	Char	15	$15.00	$15.00	TRD_STCK_CD
3	TRD_TUROVR	Num	8	14	14	TRD_TUROVR

 What is the problem?

Thanks in advance.

[RW9]

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. 

[WesleyD]

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

[aminkarimid]

Here is a sample of my dataset. Thanks.

[Kurt_Bremser]

---

@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.

[aminkarimid]

Hello @Kurt_Bremser

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.

[Kurt_Bremser]

---

@aminkarimid wrote:

Hello @Kurt_Bremser

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.

[aminkarimid]

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 @Kurt_Bremser from the .zip

[Kurt_Bremser]

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?

[aminkarimid]

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.

[aminkarimid]

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.

[WesleyD]

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

[aminkarimid]

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

[Kurt_Bremser]

---

@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.

[ballardw]

---

@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