turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Obtain all possible pairs from a list

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Thursday

Hi,

I have the following data:

data have;

input element$ 1-2 qt;

datalines;

A 1

B 2

Cc3

D 4

Ee5

;

run;

I want to obtain all possible pairs like this:

A B

A Cc

A D

A Ee

B Cc

B D

...

Thanks!

Accepted Solutions

Solution

Saturday

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ilikesas

Thursday

@ilikesas wrote:

Hi PaigeMiller,

this code is quite simple, and I didn't even think of it!

But this code has repetitions, that is, since I have 5 elements it gives me all the pairs twice and also the pair of each element with itself

5sq = 25 pairs,

but if I want the unique non-same pairs I should get (25-5)/2 = 10 pairs

Thanks!

Minor modification of @PaigeMiller's code;

proc sql; create table want as select distinct a.element,b.element as elementb from have as a, have as b where a.element<b.element; quit;

You did not request unique solutions or ordered, so add the distinct to get the one group and the where to select the ordered pair.

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ilikesas

Thursday

Will this help?

```
data have;
input element$ 1-2 qt;
datalines;
A 1
B 2
Cc3
D 4
Ee5
;
run;
proc sql;
select max(qt) into :m
from have;
quit;
data want;
if _n_=1 then do;
if 0 then set have;
dcl hash H (dataset:'have') ;
h.definekey ("qt") ;
h.definedata ("element") ;
h.definedone () ;
end;
do i=1 to &m-1;
do j=i+1 to &m;
rc=h.find(key:i);
elementa=element;
rc=h.find(key:j);
elementb=element;
output;
end;
end;
keep elementa elementb;
run;
```

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to novinosrin

Thursday

Hi novinsorin,

I am not familiar with hash, is it possible to get the solution in a macro form similar to this:

proc sql;

select element

into :element_list separated by ' '

from have

;

quit;

proc sql;

select count(element)

into :n

from have

;

quit;

%put &element_list;

%put &n;

%macro m1;

data want;

%do i=1 %to &n;

%let var1=%scan(&element_list,&i);

var1=&var1;

%end;

%mend m1;

%m1

Thank you

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ilikesas

Thursday

I have a class to attend at my college in 5 mins,. If you are familiar with proc format cntrl in/cntrl out look up formatted dataset, you could replace my hash with formats which essentially does the same. Or i can look into it in 3 hours

Alternatively, you can filter the Cartesian product given by paigemiller to only output the pairs with a where condition

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ilikesas

Friday - last edited Friday

```
data have;
input element$ 1-2 qt;
datalines;
A 1
B 2
Cc3
D 4
Ee5
;
run;
DATA fmtDataset;
SET have;
RETAIN fmtname 'fmtcode' type 'N';
RENAME qt = Start;
LABEL =element;
RUN;
PROC FORMAT CNTLIN=fmtDataset;
RUN;
proc sql;
select max(qt) into :m
from have;
quit;
data want;
do i=1 to &m-1;
do j=i+1 to &m;
elementa=put(i,fmtcode.);
elementb=put(j,fmtcode.);
output;
end;
end;
run;
```

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ilikesas

Thursday

```
proc sql;
create table want as select a.element,b.element as element1 from have as a,have as b;
quit;
```

--

Paige Miller

Paige Miller

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to PaigeMiller

Thursday

Hi PaigeMiller,

this code is quite simple, and I didn't even think of it!

But this code has repetitions, that is, since I have 5 elements it gives me all the pairs twice and also the pair of each element with itself

5sq = 25 pairs,

but if I want the unique non-same pairs I should get (25-5)/2 = 10 pairs

Thanks!

Solution

Saturday

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ilikesas

Thursday

@ilikesas wrote:

Hi PaigeMiller,

this code is quite simple, and I didn't even think of it!

But this code has repetitions, that is, since I have 5 elements it gives me all the pairs twice and also the pair of each element with itself

5sq = 25 pairs,

but if I want the unique non-same pairs I should get (25-5)/2 = 10 pairs

Thanks!

Minor modification of @PaigeMiller's code;

proc sql; create table want as select distinct a.element,b.element as elementb from have as a, have as b where a.element<b.element; quit;

You did not request unique solutions or ordered, so add the distinct to get the one group and the where to select the ordered pair.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ilikesas

Thursday

@ilikesas wrote:

Hi PaigeMiller,

this code is quite simple, and I didn't even think of it!

5sq = 25 pairs,

but if I want the unique non-same pairs I should get (25-5)/2 = 10 pairs

Thanks!

That's not all possible, that's all possible unique combinations, irrespective of order.

Another method is using ALLCOMB().

See this example here.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

Thursday

These combinations include duplicates and same-element combinations, whereas I don't want these duplicates and same element combinations - how would I in this case delete the duplicates and same value combs?

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ilikesas

Thursday - last edited Thursday

Sorry, I forgot the link.

```
%let n=5; /* total number of items */
%let k=2; /* number of items per row */
/* generate combinations of n items taken k at a time */
data Comb(keep=c1-c&k);
array c[&k] (&k.*0); /* initialize array to 0 */
array list[&n] _temporary_ (1, 2, 3, 4, 5); /*values in the array to set into groups of 2*/
ncomb = comb(&n, &k); /* number of combinations */
do j = 1 to ncomb;
rc = lexcombi(&n, &k, of c[*]);
do i=1 to dim(c);
c(i)=list(c(i));
end;
output;
end;
run;
```

Edit: and forgot it again:

https://blogs.sas.com/content/iml/2014/07/28/lexicographic-combinations.html

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ilikesas

Friday

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ilikesas

Friday

@ilikesas wrote:

Hi PaigeMiller,

this code is quite simple, and I didn't even think of it!

5sq = 25 pairs,

but if I want the unique non-same pairs I should get (25-5)/2 = 10 pairs

Thanks!

As @ballardw (and probably others) have pointed out ... you can then reduce the number of pairs to what you want via any number of methods in SAS (SQL or data step) ... but your original problem did not ask for "unique non-same pairs", it asked for "all possible pairs" (in fact, you asked for "all possible pairs" twice, once in the title and once in the text of your original message).

--

Paige Miller

Paige Miller

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ilikesas

Friday

```
data have;
input element$ 1-2 qt;
datalines;
A 1
B 2
Cc3
D 4
Ee5
;
data want;
set have end=last nobs=nobs;
array x{999} $ 32 _temporary_;
x{_n_}=element;
if last then do;
do i=1 to nobs-1;
var1=x{i};
do j=i+1 to nobs;
var2=x{j};output;
end;
end;
end;
keep var1 var2;
run;
```