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;
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
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
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
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
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
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.
ok, 10x
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.