BookmarkSubscribeRSS Feed
cindyforest7
Calcite | Level 5

Hello everyone,

I have a list of providers, and I want to create a second dataset with each provider paired with one other provider. A provider will not be paired to itself.

table have

provider_id

a

b

c

d

e

....

table want

provider id1         provider id2

a                           b

a                           c

a                           d

a                           e

b                           c

b                           d

b                           e

c                            d

c                            e

d                            e

Thanks advance for all the inputs!!!

4 REPLIES 4
ballardw
Super User

It appears that you have a second requirement that if the a-b pair has been generated that you do not want b-a. Is that correct?

cindyforest7
Calcite | Level 5

Correct. Smiley Happy

stat_sas
Ammonite | Level 13

data have;

input provider_id $;

datalines;

a

b

c

d

e

;


proc sql;

create table want as

select a.provider_id as provider_id1, b.provider_id as provider_id2 from have a

cross join have b

where a.provider_id<b.provider_id;

quit;

Ksharp
Super User

You are trying to get all the combination of ID . OR try to use function  COMB() .

data have;
input provider_id $;
datalines;
a
b
c
d
e
;
run;
data want;
 set have end=last;
 array x{9999999} $ _temporary_;
 x{_n_}=provider_id;
 if last then do;
  do i=1 to _n_-1;
   do j=i+1 to _n_;
     id1=x{i};id2=x{j};output;
   end;
  end;
end;
keep id1 id2;
run;


Xia Keshan

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
  • 4 replies
  • 1012 views
  • 0 likes
  • 4 in conversation