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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User
Use time literals.
Where MYVAR between '09:00't and '12:00't

View solution in original post

18 REPLIES 18
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
SAS Employee
Could you add the dataset sample03 and sample04(or an extract from it) to your post in text? This seems OK to me.
aminkarimid
Lapis Lazuli | Level 10

Here is a sample of my dataset. Thanks.

Kurt_Bremser
Super User

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

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
Super User

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

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
Super User

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
Lapis Lazuli | Level 10
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
Lapis Lazuli | Level 10
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
SAS Employee
Then your code was fine, but replace the "12" with "11" so it will include all the timestamps up to 11:59:59
aminkarimid
Lapis Lazuli | Level 10
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
Super User

@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
Super User

@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 

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
  • 18 replies
  • 1299 views
  • 5 likes
  • 6 in conversation