Counting concurrency and grouping per event

_Dan_ — Fri, 15 May 2020 12:03:24 GMT

Hi,

I'm trying to report on concurrent groups of events. We capture data every 15 minutes across 7 Servers / 14 Work spaces. I'm tracking where there's 100% utilisation of any of those 14 Work spaces.

The Concurrency flag is done. I have a '1' where an event existed for two or more rows (two or more 15 minute periods). Simple SQL - most likely a lag/lead way to do it, but this works. Basically the _Get dataset has the original RunDtTm, plus two more columns where the RunDtTm has been shifted forward by 15 minutes (RunDtTm15), or backwards by 15 minutes (RunDtTm15_).

proc sql;
	create table _Stage as
		select
			datepart(o.RunDtTm) as RunDt format DDMMYY10.
			,o.RunDtTm
			,case when coalesce(b.RunDtTm,f.RunDtTm) is not null then 1 else 0 end as Concurrent
			,o.Server
			,o.WorkFolder
			,o.Use_pct
		from
			_Get		o	left join
			_Get		f	on o.RunDtTm = f.RunDtTm15 and o.Server = f.Server and o.WorkFolder = f.WorkFolder left join
			_Get		b	on o.RunDtTm = b.RunDtTm15_ and o.Server = b.Server and o.WorkFolder = b.WorkFolder 
;quit;

What I now need is a flag that counts how many times per day a record exists, but, counts concurrent records as one. Here's the data I have right now. The best example would be 22/04/2020, Server03, work2 - there were three events, but two were concurrent episodes.

Event 1: 22APR2020:09:00:00

Event 2: 22APR2020:10:30:00 - 22APR2020:11:00:00

Event 3: 22APR2020:11:15:00 - 22APR2020:12:00:00

And thus the new column would show 1, 2, 2, 2, 3, 3, 3, 3.

Here's the contents of the data _Stage from the above query.

DATA WORK._STAGE;
    LENGTH
        RunDt              8
        RunDtTm            8
        Concurrent         8
        Server           $ 8
        WorkFolder       $ 5
        Use_pct          $ 4 ;
    FORMAT
        RunDt            DDMMYY10.
        RunDtTm          DATETIME18.
        Concurrent       BEST1.
        Server           $CHAR8.
        WorkFolder       $CHAR5.
        Use_pct          $CHAR4. ;
    INFORMAT
        RunDt            DDMMYY10.
        RunDtTm          DATETIME18.
        Concurrent       BEST1.
        Server           $CHAR8.
        WorkFolder       $CHAR5.
        Use_pct          $CHAR4. ;
    INFILE DATALINES4
        DLM='7F'x
        MISSOVER
        DSD ;
    INPUT
        RunDt            : ?? DDMMYY10.
        RunDtTm          : ?? ANYDTDTM18.
        Concurrent       : ?? BEST1.
        Server           : $CHAR8.
        WorkFolder       : $CHAR5.
        Use_pct          : $CHAR4. ;
DATALINES4;
02/04/202002APR2020:16:00:000server06work2100%
03/04/202003APR2020:09:30:000server07work2100%
07/04/202007APR2020:09:45:000server03work2100%
07/04/202007APR2020:18:45:000server06work1100%
08/04/202008APR2020:11:00:000server06work2100%
08/04/202008APR2020:11:30:000server06work2100%
09/04/202009APR2020:09:30:000server02work2100%
09/04/202009APR2020:19:45:000server06work2100%
13/04/202013APR2020:14:00:000server02work2100%
14/04/202014APR2020:15:45:000server02work2100%
14/04/202014APR2020:15:45:000server05work2100%
15/04/202015APR2020:07:45:000server03work2100%
15/04/202015APR2020:09:15:000server02work2100%
15/04/202015APR2020:11:15:000server03work2100%
16/04/202016APR2020:09:00:001server03work2100%
16/04/202016APR2020:09:15:001server03work2100%
16/04/202016APR2020:09:30:001server03work2100%
16/04/202016APR2020:09:45:001server03work2100%
16/04/202016APR2020:11:15:000server03work2100%
16/04/202016APR2020:12:00:000server03work1100%
17/04/202017APR2020:09:00:000server02work2100%
20/04/202020APR2020:09:15:000server05work1100%
20/04/202020APR2020:12:00:000server06work2100%
22/04/202022APR2020:09:00:000server03work2100%
22/04/202022APR2020:10:30:001server03work2100%
22/04/202022APR2020:10:45:001server03work2100%
22/04/202022APR2020:11:00:001server03work2100%
22/04/202022APR2020:11:15:000server02work2100%
22/04/202022APR2020:11:15:001server03work2100%
22/04/202022APR2020:11:30:001server03work2100%
22/04/202022APR2020:11:45:001server03work2100%
22/04/202022APR2020:12:00:001server03work2100%
23/04/202023APR2020:04:45:000server02work1100%
23/04/202023APR2020:14:15:000server04work2100%
24/04/202024APR2020:12:30:000server06work1100%
27/04/202027APR2020:04:30:000server03work2100%
28/04/202028APR2020:08:15:000server03work1100%
28/04/202028APR2020:10:30:001server05work2100%
28/04/202028APR2020:10:45:001server05work2100%
28/04/202028APR2020:13:00:000server04work2100%
28/04/202028APR2020:17:15:000server07work2100%
29/04/202029APR2020:07:15:000server06work2100%
29/04/202029APR2020:14:30:000server05work2100%
29/04/202029APR2020:15:15:000server03work2100%
30/04/202030APR2020:11:30:000server06work2100%
30/04/202030APR2020:14:15:000server06work1100%
30/04/202030APR2020:16:00:000server03work1100%
;;;;

Here's what I want it to look like (take note of entry 5 which is a different Sever, and hence the Concurrent_Count is 1):

RunDt	RunDtTm	Concurrent	Server	WorkFolder	Use_pct   Concurrent_Count
22/04/2020	22APR2020:09:00:00	0	geniegrid03	/sas/workb	100%     1
22/04/2020	22APR2020:10:30:00	1	geniegrid03	/sas/workb	100%     2
22/04/2020	22APR2020:10:45:00	1	geniegrid03	/sas/workb	100%     2
22/04/2020	22APR2020:11:00:00	1	geniegrid03	/sas/workb	100%     2
22/04/2020	22APR2020:11:15:00	0	geniegrid02	/sas/workb	100%     1
22/04/2020	22APR2020:11:15:00	1	geniegrid03	/sas/workb	100%     3
22/04/2020	22APR2020:11:30:00	1	geniegrid03	/sas/workb	100%     3
22/04/2020	22APR2020:11:45:00	1	geniegrid03	/sas/workb	100%     3
22/04/2020	22APR2020:12:00:00	1	geniegrid03	/sas/workb	100%     3

Re: Counting concurrency and grouping per event

RichardDeVen — Fri, 15 May 2020 16:47:03 GMT

You are having different concurrent_count values tracked for different servers. You explanation shows the count increasing, so in this answer that value will be called seq for sequence Server is a character value, and tracking by value can be easily done using a HASH object. They key is the `server` and the data is the `seq` that may or may not get incremented according to your rules.

The hash should be cleared at the start of the date when the sequence counting is reset.

Example:

Data is presumed to be in a timestamp order essential to sequence count computation.

data want; if 0 then set _STAGE; if _n_ = 1 then do; declare hash cc(); cc.defineKey('server'); cc.defineData('seq'); cc.defineDone(); call missing(seq); * prevent warning; pserver = server; * prep pdv; end; cc.clear(); pserver = ''; * act as a LAG var for server; pflag = .; * act as a LAG var for concurrent flag; * one day at a time; do until (last.rundt); set _STAGE; by rundt; rc = cc.find(); * side effect is that seq for server retrieved from hash; if pserver ne server then do; * server changed from prior row; if rc ne 0 then seq = 1; * first time for server; else seq + 1; * first time for server in a subsequent run, seq+1 regardless of concurrent; end; else do; /* same server as prior row */ /* a hash entry MUST exist if code flow reaches here */ /* which means seq is value for current server */ if concurrent = 0 then seq + 1; * not concurrent always increases seq; else seq + (pflag = 0); * concurrent run of 0 1 becomes seq+1, and 1 1 gets seq+0; end; cc.replace(); * save seq count for server; OUTPUT; pflag = concurrent; * update prior value tracking variables (lag emulation); pserver = server; drop rc pflag pserver; end; run;

Happy counting!

Re: Counting concurrency and grouping per event

_Dan_ — Mon, 18 May 2020 10:23:30 GMT

Good lord, you've put just as much effort into that reply as I put into creating and debugging the process on Friday. I ran it and it's perfect ,thank-you.

Now all I have to do is learn how it works and keep it in mind for future usage.

topic Re: Counting concurrency and grouping per event in SAS Programming

Counting concurrency and grouping per event

Re: Counting concurrency and grouping per event

Re: Counting concurrency and grouping per event