topic Re: How to use "where statement" for bounding time? in SAS Programming

How to use "where statement" for bounding time?

aminkarimid — Fri, 28 Jul 2017 10:30:50 GMT

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.

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

RW9 — Fri, 28 Jul 2017 10:39:12 GMT

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.

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

WesleyD — Fri, 28 Jul 2017 10:43:26 GMT

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

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

aminkarimid — Fri, 28 Jul 2017 11:37:38 GMT

Here is a sample of my dataset. Thanks.

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

Kurt_Bremser — Fri, 28 Jul 2017 11:41:20 GMT

@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-data-AKA-generate/ta-p/258712 to convert your dataset to a data step and post that here.

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

WesleyD — Fri, 28 Jul 2017 12:08:22 GMT

I don't see any valid timestamps in there.

Also note that your datastep will include the timestamps up to 1 and not up to 12, since the between statement will include all timestamps with an HOUR of 12 (so up to 12:59:59). This might be the purpose, but I'd like to point it out...

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

aminkarimid — Fri, 28 Jul 2017 12:54:32 GMT

So, Would you please tell me how can I fix this problem?

If you know another codes for this purpose, please tell me.

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

aminkarimid — Fri, 28 Jul 2017 12:59:33 GMT

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.

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

Kurt_Bremser — Fri, 28 Jul 2017 13:02:53 GMT

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

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

aminkarimid — Fri, 28 Jul 2017 13:27:39 GMT

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

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

Tom — Fri, 28 Jul 2017 13:26:46 GMT

Why not just use time literals in your where statement?

where TRD_EVENT_TM between '09:00't and '12:59:59't;

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

Kurt_Bremser — Fri, 28 Jul 2017 13:31:32 GMT

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?

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

aminkarimid — Fri, 28 Jul 2017 13:44:38 GMT

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.

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

aminkarimid — Fri, 28 Jul 2017 13:49:46 GMT

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.

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

WesleyD — Fri, 28 Jul 2017 13:52:14 GMT

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

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

Tom — Fri, 28 Jul 2017 13:53:39 GMT

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

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

aminkarimid — Fri, 28 Jul 2017 14:07:35 GMT

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

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

Kurt_Bremser — Fri, 28 Jul 2017 15:50:57 GMT

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

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

ballardw — Fri, 28 Jul 2017 16:00:51 GMT

@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