Determine flow references

[Loko]

Dear All,

For the attached tab-delimited file I need to determine the process flow of the active processes.

To identify the active process the condition is missing(datef)=1.

For example Process 682 is active and it comes from 634. BUT 632 is opened and closed in the current month
(datas_ini = 2JAN2015 and datef_ini=5JAN2015). Therefore we have to look back at 632 and determine where it comes from.
632 comes from 447, 510, 559. BUT since 510 is opened and closed in the current month it is ignored.
Eventually the correct result is 682-447 and 682-559.

The code I have build does not work for all the cases.

Let's take process no 9. 9 comes from 7.
BUT 7 is opened and closed in the current month.
Looking back at 7 we see that 7 comes from 3, 5, 6.
Since 3 is closed we look back at it and notice that 3 comes from 4.
4 is closed - we look back at it and we notice that 4 comes from 44.
Therefore 7 comes from 44.
BUT 7 also comes from 5 and 6 since those processes are active.
Unfortunately the codes does not write to output - 7-44, 7-5 and 7-6. It only writes 7-44.

The correspondence of the variables is ky-process-datef-datas and ky_ini_ini-process_ini-datef_ini-datas_ini.

Here is my code:

%let &data_lun_ant="31DEC2015"d;

data want ;

length ky_ini_hsh $ 30;
if 0 then set have;

if _n_ eq 1 then do;
CALL MISSING(process_ini_hsh,datas_ini_hsh,datef_ini_hsh);
call missing(ky_ini_hsh);
declare hash ir(dataset:"have(rename=(process_ini=process_ini_hsh
datas_ini=datas_ini_hsh datef_ini=datef_ini_hsh ky_ini=ky_ini_hsh
) where=(datas gt &data_lun_ant and datef gt &data_lun_ant))", multidata:'Y');
ir.definekey('ky');
ir.definedata('process_ini_hsh','datas_ini_hsh', 'datef_ini_hsh', 'ky_ini_hsh');
ir.definedone();

end;

set have(where=(missing(datef)=1));

if datas_ini gt &data_lun_ant and datef_ini gt &data_lun_ant then
do;

rc=ir.find(key:ky_ini);
do while (rc=0);
  if datas_ini_hsh gt &data_lun_ant and datef_ini_hsh gt &data_lun_ant then
do;

_ky_ini_hsh=ky_ini_hsh;
_ky_ini=ky_ini;
rc1=ir.find_next(key:_ky_ini);
rc=ir.find(key:_ky_ini_hsh);
if rc1=0 then rc=0;
end;
  else do;
   conne=catx('-',process,process_ini_hsh);
   output;
   rc=ir.find_next(key:ky_ini);
  end;
end;
end;

else do;conne=catx('-',process,process_ini);output;end;

run;

[Ksharp · Accepted solution]

Hard to catch on you . Too many uncertain things.

data x;
input ky $ ky_ini $ process $ process_ini $ (datef   datas   datas_ini   datef_ini ) (: ddmmyy10.);
format datef   datas   datas_ini   datef_ini  ddmmyy10.;
cards;
CL632   CL447   632 447 02/01/2015  02/01/2015      . .
CL632   CL510   632 510 02/01/2015  02/01/2015  02/01/2015  05/01/2015
CL632   CL559   632 559 02/01/2015  02/01/2015      .  .
CL682   CL632   682 632     .   02/01/2015  02/01/2015 .
CL684   CL632   684 632     .   02/01/2015  02/01/2015 .
LC1563  SV560   1563    560 02/01/2015  02/01/2015  . . 
LC537   LC1563  537 1563    02/01/2015  02/01/2015  02/01/2015  02/01/2015
LC2537  LC537   2537    537 02/01/2015  02/01/2015  02/01/2015  02/01/2015
SV20000 LC2537  20000   2537        .   02/01/2015  02/01/2015 .
SV20000 LC2537  20000   2537        .   02/01/2015  02/01/2015 .
SV20000 LC2537  20000   2537        .   02/01/2015  02/01/2015 .
LC1 LC1112  1   1112    02/01/2015  02/01/2015  . . 
LC2 LC1112  2   1112        .       .  .  .
LC10    LC1 10  1       .   02/01/2015  02/01/2015 .
LC11    LC1 11  1       .   02/01/2015  02/01/2015 .
LC13    LC1 13  1       .   02/01/2015  02/01/2015 .
LC4 LC44    4   44  02/01/2015  02/01/2015  . . 
LC3 LC4 3   4   02/01/2015  02/01/2015  01/02/2015  02/01/2015
LC7 LC3 7   3   02/01/2015  02/01/2015  01/02/2015  02/01/2015
LC7 LC5 7   5   02/01/2015  02/01/2015  . . 
LC7 LC6 7   6   02/01/2015  02/01/2015  . . 
LC9 LC7 9   7       .   02/01/2015  02/01/2015 .
LC9 LC8 9   8       .  . .  .                 
;
run;
data have;
 set x(rename=(process=from process_ini=to) );
 length flag $ 1;
 if  not missing(datas_ini) and not missing(datef_ini) then flag='*';
 keep from to flag;
run;
data ancestor;
if _n_ eq 1 then do;
 if 0 then set have;
 declare hash h(dataset:'have',hashexp:20);
 h.definekey('to');
 h.definedone();
end;
 set have;
 if h.check(key:from) ne 0;
run;


data want (keep =  path);
if _n_ eq 1 then do;
length path _path  $ 2000 ;
if 0 then set have;
declare hash ha(hashexp:20,dataset:'have(where=(From is not missing and To is not missing))',multidata:'Y');
ha.definekey('From');
ha.definedata('To');
ha.definedone();
declare hash pa(ordered:'Y');
declare hiter hi_path('pa');
pa.definekey('count');
pa.definedata('path');
pa.definedone();
end;
set ancestor;
count=1;
path=catx(' ',From,To);
pa.add();
do while(hi_path.next()=0);
_path=path;   
From=scan(path,-1,' '); 
rc=ha.find();
if rc ne 0 then output;
do while(rc=0);
  if not find(path,strip(To)) then do;
                                      count+1;
                                      path=catx(' ',path,To);
                                      pa.add();
                                      path=_path;
  end;
  rc=ha.find_next();
end;
end;
pa.clear();
run;


data key;
 set have(where=(flag='*'));
 key=from;output;
 key=to;output;
 keep key;
run;
data final_want;
if _n_ eq 1 then do;
 if 0 then set key;
 declare hash h(dataset:"key");
 h.definekey('key');
 h.definedone();
end;
set want;
length want start $ 20;
start=scan(path,1);
do i=2 to countw(path);
  key=scan(path,i);
  if h.check() ne 0 then do;want=catx('-',start,key);output;end;
end;
keep start want ;
run;

Xia Keshan

[Loko]

Hello,

Yes I know it is difficult to explain

But your code works almost perfectly.

The only problem is with 10-1, 11-1, 13-1 from the final output.

This connection should not exist since 1 has been closed in the month (datas - datas_ini in JANUARY).

10x

[Ksharp]

That is what I am confused with .

You said " is opened and closed in the current month (datas_ini = 2JAN2015 and datef_ini=5JAN2015)"

But

LC3 LC4 3   4   02/01/2015  02/01/2015  01/02/2015  02/01/2015

LC7 LC3 7   3   02/01/2015  02/01/2015  01/02/2015  02/01/2015

Their month are not the same, while you still get rid of them , that is right ?

"This connection should not exist since 1 has been closed in the month (datas - datas_ini in JANUARY)."

That is not ture. Base on your data , datef_ini is missing value .

LC1 LC1112  1   1112    02/01/2015  02/01/2015 . .

LC2 LC1112  2   1112        .       .  .  .

LC10    LC1 10  1       .   02/01/2015 02/01/2015 .

LC11    LC1 11  1       .   02/01/2015  02/01/2015 .

LC13    LC1 13  1       .   02/01/2015  02/01/2015 .

Xia Keshan

[Loko]

You are right. When I have built the dataset example I have mistyped date values for that sequence.

datas_ini and datef_ini should have been in the same month for process 1.

I have changed the values in the example, and the code gives the expected results.

10x

[Ksharp]

Oh. If their month should be the same. You also need to change my code :

if  not missing(datas_ini) and not missing(datef_ini) then flag='*';

-->

if  month(datas_ini) = month(datef_ini) then flag='*';

Message was edited by: xia keshan Sorry. A problem found.

[Loko]

ok, 10x