topic Re: Create a flow direction in proc sql in SAS Programming

Create a flow direction in proc sql

lpy0521 — Fri, 26 Oct 2018 16:39:41 GMT

Hi, there.

Just curious how to achieve this in sas. Basically I want to tract each id's full life cycle from the origin to destination as below example shows:

have:
ID	from	to
1	a	b
1	b	c
1	c	d
2	e	f
2	f	g

want:
ID	from	to/from	to/from	to/from
1	a	b	c	d
2	e	f	f	g

Any comment is highly appreciated.

Re: Create a flow direction in proc sql

novinosrin — Fri, 26 Oct 2018 16:33:56 GMT

why is f missing if id 2 traveled from e to f and then from f to g?

Re: Create a flow direction in proc sql

lpy0521 — Fri, 26 Oct 2018 16:40:11 GMT

Sorry it was a typo. Thanks for pointing out!

Re: Create a flow direction in proc sql

mkeintz — Fri, 26 Oct 2018 16:54:04 GMT

Do you have ~~sas/ets~~ (sorry, I meant SAS/OR)? It includes proc optnet, which will directly solve your problem.

Run

proc setinit;

run;

and see ~~if sas/ets~~ sas/or is listed.

Re: Create a flow direction in proc sql

lpy0521 — Fri, 26 Oct 2018 16:56:08 GMT

It looks like I have sas/or.
Never use it before, please guide me.
Thanks.

Re: Create a flow direction in proc sql

novinosrin — Fri, 26 Oct 2018 16:57:30 GMT

Yes look for OR as Mark suggested.

But just to clear the air a bit:

I would have thought the logical result to be

want:
ID	from	to/from	to/from	to/from
1	a	b	c	d
2	e	f	g

instead of

want:
ID	from	to/from	to/from	to/from
1	a	b	c	d
2	e	f	f	g

Can you correct my understanding?

Re: Create a flow direction in proc sql

lpy0521 — Fri, 26 Oct 2018 17:04:01 GMT

Yes, your logic is correct, The reason for me to create that two fs is just to align the column such that I can use the last column as the final destination, for future analysis.
Thanks.

Re: Create a flow direction in proc sql

novinosrin — Fri, 26 Oct 2018 17:10:09 GMT

Ok Thanks. Let's together wait if Mark has the time to demo or lead us with some guidance. I am excited about OR too. Thankfully I haven't lost much hair after having learned base sas fairly well. Otherwise your req is a simple double proc transpose

Re: Create a flow direction in proc sql

Ksharp — Sat, 27 Oct 2018 11:22:23 GMT

data have;
input ID	from $	to $ ;
rename from=_start to=_end;
cards;
1	a	b
1	b	c
1	c	d
2	e	f
2	f	g
;
run;
 
proc sql;
create table x as
 select * from have
 where catx(' ',id,_start) not in 
(select catx(' ',id,_end) from have);
quit; 

data want(keep=id path);
if _n_ eq 1 then do;
length path _path  $ 700 ;
if 0 then set have;
declare hash ha(hashexp:20,dataset:'have(where=(_start is not missing and _end is not missing))',multidata:'y');
ha.definekey('_start');
ha.definedata('_end');
ha.definedone();

declare hash pa(ordered:'y');
declare hiter hi_path('pa');
pa.definekey('n');
pa.definedata('n','path');
pa.definedone();

end;

set x;
count=1;n=1;_n=1;
path=catx('|',_start,_end);
   
pa.add();
do while(hi_path.next()=0);
 if n ne 1 then pa.remove(key:_n);_n=n;
 _path=path;   
 _start=scan(path,-1,'|');
 rc=ha.find(); if rc ne 0 then output;
 do while(rc=0);
  if not findw(path,strip(_end),'|') then do;
   if length(path)+length(_end)+1 gt lengthc(path) then do;
    putlog 'ERROR: The length of path and _path are set too short';
    stop;
   end;
   
   count+1;n=count;
   path=catx('|',path,_end);
   pa.add();
   path=_path;
 end;
 else output;
  rc=ha.find_next();
end;
end;
pa.clear();

run;

Re: Create a flow direction in proc sql

mkeintz — Mon, 29 Oct 2018 04:50:58 GMT

Having used proc optnet for other from/to linkage tasks, I thought this problem would be convenient to solve. But it's not:

proc optnet doesn't honor BY groups, so you'd have to run it multiple times, and then concatenate all the by-group results.
Within each ID group you have to ask proc optnet to generate the longest path (a-d is longer than all the others). If would provide 2 such paths for ID group 1: a->b->c->d and d->c->b->a. You'd have to compare the path output file to other output files from optnet to determine which one honors the flow direction desired, and then arrange data to be ready for transpose from tall to wide. A pain you-know-where.

So here's two step solution

In a data step
1. make a hash call triple, which
  1. Includes ID as a key in the hash object, making honoring id groups easier
  2. Makes a triple for each node in the path, containing (in addition to ID) NODE, PRIOR, and NEXT
2. At the end of each by group
  1. Find the source (the node with no prior)
  2. output each successive node until reaching the sink (the node with no next)
  3. Label the nodes as "source", "node_2", "node_3", ... to "sink".
After the data step run a proc transpose:

EDITTED NOTE: To accommodate a single record ID, a node value has to be arbitrarily assigned prior to the triple.find() method. That statement ("if missing(node) then node=from;") is included below:

data have ;
  input ID from :$1. to :$1.;
datalines;
1 b c 
1 c d 
1 a b 
2 e f 
2 f g 
run;

data need (keep=id nodename node);  
  set have;
  by id;
  if _n_=1 then do;
    length prior node next $1;
	declare hash triples();
	  triples.definekey('id','node');
	  triples.definedata('id','node','prior','next');
	  triples.definedone();
  end;

  /*Populate triples hash */
  if triples.find(key:id,key:from)^=0 then call missing(prior);
  triples.replace(key:id,key:from,data:id,data:from,data:prior,data:to);

  if triples.find(key:id,key:to)^=0 then call missing(next);
  triples.replace(key:id,key:to,data:id,data:to,data:from,data:next);

  if last.id;
  triples.output(dataset:'triples');

  if missing(node) then node=from;  /*Added to accommodate single-link ID's*/
  triples.find();
  if not missing(prior) then do until (missing(prior));
    triples.find(key:id,key:prior);
  end;

  nodename='Source'; /* Output nodes from source to sink */
  output;
  if not missing(next) then do seq=2 by 1 until(missing(next));
    triples.find(key:id,key:next);
	if missing(next) then nodename='Sink';
    else nodename=cats('Node_',seq);
    output; 
  end;
run;

proc transpose data=need out=want (drop=_name_) ;
  by id;
  var node;
  id nodename;
run;

The only part of the original request unsatisfied here is that some internal values (such as NODE_3 for id group 2) are missing. That should be easy to fix, if really necessary.

Re: Create a flow direction in proc sql

novinosrin — Mon, 29 Oct 2018 01:32:43 GMT

To OP ,Mark, Xia who may have a couple of mins to clarify plz

I had this question printed and showed that to a friend of mine who is majoring in data science.

He pointed out. Isn't it always the following L shape that's the flow

have:
ID	from	to
1	a	b
1	b	c
1	c	d
2	e	f
2	f	g

Can you provide some intuition?

Re: Create a flow direction in proc sql

mkeintz — Mon, 29 Oct 2018 04:19:46 GMT

I wrote my program to accommodate any sequence of links within an ID group. Although the example had each link descend from the prior record, the OP didn't state that as a condition. But if that condition can be guaranteed to always be the case then the program is much simpler. Just copy the sequence of FROM values and append the final TO value for each ID. Then transpose:

data have;
  input id  from :$1. to :$1.;
datalines;
1 a b 
1 b c 
1 c d 
2 e f 
2 f g 
run;

data vneed / view=vneed;
  set have;
  by id;
  seq+1;
  if first.id then seq=1;
  if first.id then nodename='Source';
  else nodename=cats('Node_',seq);
  output;
  if last.id ;
  from=to;
  nodename='Sink';
  output;
run;

proc transpose data=vneed out=want (drop=_name_);
  by id;
  var from;
  id nodename;
run;

Re: Create a flow direction in proc sql

lpy0521 — Mon, 29 Oct 2018 04:29:04 GMT

Thanks a lot for the explanation. Hash is absolutely new to me and just test the code but give an error when I am adding new case: there only contains source and sink node as id3? Would you please show me how can i modify the code accordingly? Thanks!!!

data have ;
  input ID from :$1. to :$1.;
datalines;
1 b c 
1 c d 
1 a b 
2 e f 
2 f g 
3 x y
run;

Re: Create a flow direction in proc sql

lpy0521 — Mon, 29 Oct 2018 04:31:30 GMT

Thanks a lot! I tested the code, it gives me same id with various different path. I will need to closely investigate my data source.

Re: Create a flow direction in proc sql

mkeintz — Mon, 29 Oct 2018 04:33:49 GMT

Please show the log containing the error message and related source code.

Re: Create a flow direction in proc sql

lpy0521 — Mon, 29 Oct 2018 04:51:17 GMT

data have ;
  input ID from :$1. to :$1.;
datalines;
1 b c 
1 c d 
1 a b 
2 e f 
2 f g 
3 x y
run;

data need (keep=id nodename node);  
  set have;
  by id;
  if _n_=1 then do;
    length prior node next $1;
	declare hash triples();
	  triples.definekey('id','node');
	  triples.definedata('id','node','prior','next');
	  triples.definedone();
  end;

  /*Populate triples hash */
  if triples.find(key:id,key:from)^=0 then call missing(prior);
  triples.replace(key:id,key:from,data:id,data:from,data:prior,data:to);

  if triples.find(key:id,key:to)^=0 then call missing(next);
  triples.replace(key:id,key:to,data:id,data:to,data:from,data:next);

  if last.id;
  triples.output(dataset:'triples');

  triples.find();
  if not missing(prior) then do until (missing(prior));
    triples.find(key:id,key:prior);
  end;

  nodename='Source'; /* Output nodes from source to sink */
  output;
  if not missing(next) then do seq=2 by 1 until(missing(next));
    triples.find(key:id,key:next);
	if missing(next) then nodename='Sink';
    else nodename=cats('Node_',seq);
    output; 
  end;
run;

proc transpose data=need out=want (drop=_name_) ;
  by id;
  var node;
  id nodename;
run;

basically I create a new case with id3, there is only two nodes x and y. The error in the log file shows:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

56 data have ;

57 input ID from :$1. to :$1.;

58 datalines;

NOTE: The data set WORK.HAVE has 6 observations and 3 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

65 run;

67 data need (keep=id nodename node);

68 set have;

69 by id;

70 if _n_=1 then do;

71 length prior node next $1;

72 declare hash triples();

73 triples.definekey('id','node');

74 triples.definedata('id','node','prior','next');

75 triples.definedone();

76 end;

78 /*Populate triples hash */

79 if triples.find(key:id,key:from)^=0 then call missing(prior);

80 triples.replace(key:id,key:from,data:id,data:from,data:prior,data:to);

82 if triples.find(key:id,key:to)^=0 then call missing(next);

83 triples.replace(key:id,key:to,data:id,data:to,data:from,data:next);

85 if last.id;

86 triples.output(dataset:'triples');

88 triples.find();

89 if not missing(prior) then do until (missing(prior));

90 triples.find(key:id,key:prior);

91 end;

93 nodename='Source'; /* Output nodes from source to sink */

94 output;

95 if not missing(next) then do seq=2 by 1 until(missing(next));

96 triples.find(key:id,key:next);

97 if missing(next) then nodename='Sink';

98 else nodename=cats('Node_',seq);

99 output;

100 end;

101 run;

NOTE: Variable node is uninitialized.

NOTE: The data set WORK.TRIPLES has 4 observations and 4 variables.

NOTE: The data set WORK.TRIPLES has 7 observations and 4 variables.

NOTE: The data set WORK.TRIPLES has 9 observations and 4 variables.

ERROR: Key not found.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: There were 6 observations read from the data set WORK.HAVE.

WARNING: The data set WORK.NEED may be incomplete. When this step was stopped there were 8 observations and 3 variables.

WARNING: Data set WORK.NEED was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

real time 0.04 seconds

cpu time 0.02 seconds

102

103 proc transpose data=need out=want (drop=_name_) ;

104 by id;

105 var node;

106 id nodename;

107 run;

NOTE: There were 8 observations read from the data set WORK.NEED.

NOTE: The data set WORK.WANT has 3 observations and 5 variables.

NOTE: PROCEDURE TRANSPOSE used (Total process time):

real time 0.01 seconds

cpu time 0.00 seconds

108

109

110 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

122

Thanks!!!

Re: Create a flow direction in proc sql

Ksharp — Mon, 29 Oct 2018 12:22:50 GMT

No. It can be settled randomly.
Or have a dead loop like:


ID	from	to
1	a	b
1	b	c
1	c	d
1      d      a