Create combination of value and name

[hhchenfx]

Hi All,

I have a data of 2 columns: Name and value.

Name and value are unique pair.

I want to create combination of 2 name and value.

For example, the output should be like

a_name | a_value | b_name | b_value

v1 3 v2 0

v1 3 v2 1

v1 3 v3 30

v1 3 v3 35

v1 9 v2 0

v1 9 v2 1

.....

Thank you,

HHC

data have;
input name $ value;
datalines;
v1 3
v1 9
v1 2
v2 0
v2 1
v3 30
v3 35
;run;

[acordes]

data have;
input name $ value;
datalines;
v1 3
v1 9
v1 2
v2 0
v2 1
v3 30
v3 35
;run;

proc sql;
create table want as 
select a.*, b.name as name2, b.value as value2
from have a cross join have b
where a.name<>b.name and a.value<>b.value;
quit;

[hhchenfx]

This sql join method will create "duplicate" combination.

proc sql;
create table want as 
select a.*, b.name as name2, b.value as value2
from have a cross join have b
where a.name<>b.name;
quit;

data dup; set want;
if name ='v1' and value=3 and name2='v2' and value2=0 then output;
if name ='v2' and value=0 and name2='v1' and value2=3 then output;
run;

[Reeza]

Looks like a cross join, you don't show any V1, V1 pairs, is that intentional?

[hhchenfx]

V1 V1 combi is not needed, Reeza.

HHC

[mkeintz · Accepted solution]

This is an instructional case to show the benefits of defining two hash iterators for a single hash object.

It's a hash analog of "do i=1 to n;  do j=1 to n:" where there are subsequent references to x{i} and x{j}:

data have;
input name $ value;
datalines;
v1 3
v1 9
v1 2
v2 0
v2 1
v3 30
v3 35
run;

data want (drop=_:);
  if 0 then set have (rename=(name=name1 value=value1)) 
                have (rename=(name=name2 value=value2)) ;

  declare hash h (dataset:'have (rename=(name=name2 value=value2))',ordered:'a');
    h.definekey(all:'Y');
    h.definedata(all:'Y');
    h.definedone();
  declare hiter hi ('h');
  declare hiter hj ('h');

  do _rci=hi.first() by 0 until (hi.next()^=0);
    name1=name2;
    value1=value2;
    do _rcj=hj.first() by 0 until (hj.next()^=0);
      if name1^=name2 then output;
    end;
  end;
run;