Counting concurrency and grouping per event

[_Dan_]

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

[RichardDeVen · Accepted solution]

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!

[_Dan_]

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.