BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ilikesas
Barite | Level 11

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!

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

@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.

 

View solution in original post

13 REPLIES 13
novinosrin
Tourmaline | Level 20

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;
ilikesas
Barite | Level 11

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

novinosrin
Tourmaline | Level 20

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

novinosrin
Tourmaline | Level 20
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;
PaigeMiller
Diamond | Level 26
proc sql;
    create table want as select a.element,b.element as element1 from have as a,have as b;
quit;
--
Paige Miller
ilikesas
Barite | Level 11

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! 

ballardw
Super User

@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.

 

Reeza
Super User

@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! 


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

 

Another method is using ALLCOMB().

See this example here. 

ilikesas
Barite | Level 11

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?

Reeza
Super User

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 

ChrisNZ
Tourmaline | Level 20

The solution by @ballardw yields 10 records as wanted 

1. Please accept it (or @Reeza's solution if you prefer) as the answer to your question

2. To save everyone's time, do try to ask the full question right away rather than adding conditions as you go 

PaigeMiller
Diamond | Level 26

@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! 


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
Ksharp
Super User
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 3947 views
  • 10 likes
  • 7 in conversation