topic Re: How to net a dataset? in SAS Programming

How to net a dataset?

user_ — Wed, 22 Mar 2017 09:10:33 GMT

I have the following dataset:

data have;
 format id1 id2 $30. value 8.;
 input id1 id2 value;
 datalines;
a	b	10
a	c	2
a	d	6
b	a	5
c	a	3
c	d	7
;
run;

and I would like to get the following one:

data want;
 format id1 id2 $30. value 8.;
 input id1 id2 value;
 datalines;
a	b	5
a	d	6
c	a	1
c	d	7
;
run;

In practice I have
- looked for non ordered couples across the first two rows,
- kept the couple such that the value is bigger,
- and assigned it the difference between values.

Any help would be very much appreciated.

Many thanks!!

Re: How to net a dataset?

Kurt_Bremser — Wed, 22 Mar 2017 09:50:23 GMT

data have;
 format id1 id2 $30. value 8.;
 input id1 id2 value;
 datalines;
a b 10
a c 2
a d 6
b a 5
c a 3
c d 7
;
run;

data inter;
set have;
if id1 > id2
then do;
  x = id1;
  id1 = id2;
  id2 = x;
end;
drop x;
run;

proc sort data=inter;
by id1 id2 descending value;
run;

data want;
set inter;
by id1 id2;
oldval = lag(value);
if first.id2 and last.id2 then output;
else if not first.id2
then do;
  value = oldval - value;
  output;
end;
drop oldval;
run;

creates the values, but with a slightly different order of observations and ID's within the observations.

Re: How to net a dataset?

user_ — Wed, 22 Mar 2017 13:25:31 GMT

Thank you for your reply.

the order of observations is not important, but the order of IDs is fundamental.

The pairs that appears in the final dataset needs to be the one with higher value, that is the difference between the values of the two couples having the same elements has to be positive. In the dataset Want that you suggested the value assigned to the pair (a c) should be -1 rather than 1.

Furthermore, I cannot not rely on "id1 > id2" as the id1 and id2 could be any string ($30).

Re: How to net a dataset?

Kurt_Bremser — Wed, 22 Mar 2017 13:53:32 GMT

So I slightly changed the code:

data inter;
set have;
length key $60;
if id1 > id2
then key = id2 !! id1;
else key = id1 !! id2;
run;

proc sort data=inter;
by key value;
run;

data want (keep=id1 id2 value);
set inter;
by key;
oldval = lag(value);
if first.key and last.key then output;
else if not first.key
then do;
  value = abs(oldval - value);
  output;
end;
drop oldval;
run;

The id1 > id2 is just there to unify the creation of the key variable.

If you somehow need to preserve the original order, we'd have to store _n_ in the data inter; step and sort the end result by that.