topic Re: time calculation in SAS proc sql in SAS Programming

time calculation in SAS proc sql

Geo- — Thu, 16 Aug 2018 10:36:08 GMT

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;

Re: time calculation in SAS proc sql

RW9 — Thu, 16 Aug 2018 10:52:46 GMT

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.

Re: time calculation in SAS proc sql

ChrisNZ — Thu, 16 Aug 2018 10:54:17 GMT

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;

Re: time calculation in SAS proc sql

gamotte — Thu, 16 Aug 2018 10:59:34 GMT

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#p1md4mx2crzfaqn14va8kt7qvfhr.htm

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;

Re: time calculation in SAS proc sql

Geo- — Thu, 16 Aug 2018 12:11:14 GMT

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

Re: time calculation in SAS proc sql

Geo- — Thu, 16 Aug 2018 12:11:34 GMT

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

Re: time calculation in SAS proc sql

RW9 — Thu, 16 Aug 2018 12:22:02 GMT

@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-data-AKA-generate/ta-p/258712

Re: time calculation in SAS proc sql

gamotte — Thu, 16 Aug 2018 12:25:41 GMT

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;

Re: time calculation in SAS proc sql

ballardw — Thu, 16 Aug 2018 14:50:04 GMT

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