Create a full list of combination on variables value

Accepted Solution Solved
Reply
Super Contributor
Posts: 371
Accepted Solution

Create a full list of combination on variables value

[ Edited ]

Hi Everyone,

 

From the data Have, I want to create a 2 (or 3) factors table that give a complete combination of value for each 2 (or 3) factor.

for example a0=0 and a1=5 ; a0=0 and a1=4....

 

The data I want to create is the bottom of the code.

 

Could you please help me with it?

 

Thank you.

 

HHC


data have;
input a0 a1 a2 a3 a4 a5 a6;
datalines;
0 5 9 2 0 8 1
1 4 0 1 . 5 0
2 . . . . 1 .
;
run;

 

*The desired output file;

name1 name2 value1 value2
a0 a1 0 5
a0 a1 0 4
a0 a1 1 5
...
a5 a6 1 1
a5 a6 1 0


Accepted Solutions
Solution
‎02-06-2016 11:54 PM
Respected Advisor
Posts: 4,609

Re: Create a full list of combination on variables value

Transpose and cartesian product :

 

data have;
input a0 a1 a2 a3 a4 a5 a6;
datalines; 
0 5 9 2 0 8 1
1 4 0 1 . 5 0
2 . . . . 1 .
;

data long;
length id $12;
set have;
array a a0 -- a6;
obs + 1;
do i = 1 to dim(a);
    id = vname(a{i});
    v = a{i};
    if not missing(v) then output;
    end;
keep obs id v;
run;

proc sql;
create table want as
select 
    a.id as name1,
    b.id as name2,
    a.v as value1,
    b.v as value2
from long as a inner join long as b
    on a.id < b.id
order by name1, name2, a.obs, b.obs;
quit;
PG

View solution in original post


All Replies
Solution
‎02-06-2016 11:54 PM
Respected Advisor
Posts: 4,609

Re: Create a full list of combination on variables value

Transpose and cartesian product :

 

data have;
input a0 a1 a2 a3 a4 a5 a6;
datalines; 
0 5 9 2 0 8 1
1 4 0 1 . 5 0
2 . . . . 1 .
;

data long;
length id $12;
set have;
array a a0 -- a6;
obs + 1;
do i = 1 to dim(a);
    id = vname(a{i});
    v = a{i};
    if not missing(v) then output;
    end;
keep obs id v;
run;

proc sql;
create table want as
select 
    a.id as name1,
    b.id as name2,
    a.v as value1,
    b.v as value2
from long as a inner join long as b
    on a.id < b.id
order by name1, name2, a.obs, b.obs;
quit;
PG
Super Contributor
Posts: 371

Re: Create a full list of combination on variables value

Thank you so much.

HHC

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 290 views
  • 0 likes
  • 2 in conversation