time calculation in SAS proc sql

[Geo-]

switch_time1	switch_time2
8:00:15	8:32:15
9:12:13	9:18:13
18:20:36	18:46:36

how to use the right format of time calculation in SAS proc sql？

PROC SQL;
CREATE TABLE MC_ET AS
SELECT
sum(case when switch_time2-switch_time1>5 minutes and switch_time2-switch_time1<10mintues then 1 else 0 end) as Count_of_8,
sum(case when switch_time2-switch_time1>10 minutes and switch_time2-switch_time1<15mintues then 1 else 0 end as count_of_9, ...
FROM have
;
QUIT;

[RW9]

proc sql;
  create table mc_et as
  select sum(case when 5 < intnx('minutes',switch_time1,switch_time2) < 10 then 1 else 0 end) as count_of_8,
         ...
  from   have;
quit;

Use the intnx function to find minutes difference, pop the range around the intnx function (assumes both are numeric times).  Code window which I have used above can be found as a {i} above post area - this retains code formatting and highlights it.  Again caps is like shouting.

[Geo-]

the syntax is right,how ever I got all the sum values equal 0,and the format of these two column are TIME8.

[ballardw]

---

@Geo- wrote:

the syntax is right,how ever I got all the sum values equal 0,and the format of these two column are TIME8.

---

Show exactly which code you submitted that is "right". Copy the code and any messages from the log. Paste into a code box opened with the forum's {I} icon.

I ask for that because the original Proc SQL code has a number of syntax errors

sum(case when switch_time2-switch_time1>5 minutes and

                                          ^ERROR

switch_time2-switch_time1<10mintues then 1 else 0 end) as Count_of_8,

                          ^ERROR

As a minimum: you are either referencing a variable in correctly (minutes in the first error) or a not legally name variable (10mintues) in the second error.

SAS time values are stored as seconds. So any subtraction yields a number of seconds, not minutes.

Likely the results of all of your comparisons are FALSE so you get 0 for all of the values. But I do not see how this code even runs.

[ChrisNZ]

Can't test at the moment, but pretty sure this works if these are SAS dates. Otherwise if they are strings just add an input function call.

create table MC_ET as
select sum( '00:05:00't < switch_time2-switch_time1 <= '00:10:00't ) as COUNT8 

      ,sum( '00:10:00't < switch_time2-switch_time1 <= '00:15:00't ) as COUNT9

from HAVE;

[gamotte]

hello,

Check the intck function. Your data has to be in a time format (see  how to convert a time string in the example below).

http://support.sas.com/documentation/cdl/en/lefunctionsref/63354/HTML/default/viewer.htm#p1md4mx2crz...

data have;
informat switch_time1 switch_time2 hhmmss8.;
input switch_time1 switch_time2 str_time $;

time_diff=intck('minute',switch_time1, switch_time2);

conv_time=input(str_time,hhmmss8.);

cards;
8:00:15 8:32:15 8:32:15
9:12:13 9:18:13 9:18:13
18:20:36 18:46:36 18:46:36
;
run;

[Geo-]

the syntax is right,how ever I got all the sum values equal 0,and the format of these two column are TIME8.

[RW9]

@gamotte's post should work, sorry I obviously meant intck rather than intnx.  Post some test data of what you have if it is not working for you, follow this post if needed:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

[gamotte]

Show us your code and test data so that we can see the exact problem.

This works :

data have;
informat switch_time1 switch_time2 hhmmss8.;
input switch_time1 switch_time2;
cards;
8:00:15 8:32:15 8:32:15
9:12:13 9:18:13 9:18:13
18:20:36 18:46:36 18:46:36
;
run;

proc sql;
    CREATE TABLE MC_ET AS
    SELECT case when intck("minute", switch_time1,switch_time2)>5 and intck("minute",switch_time1,switch_time2)<10 then 1 else 0 end AS betw5_10,
	       case when intck("minute", switch_time1,switch_time2)>10 and intck("minute",switch_time1,switch_time2)<15 then 1 else 0 end AS betw10_15,
        sum(CALCULATED betw5_10) as Count_of_8,
        sum(CALCULATED betw10_15) as Count_of_9
    FROM have
;
quit;