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

any idea,anyone pleasE?


data places;
input pid city $12.;
cards;
1 LosAngeles
2 Orlando
3 London
4 NewYork
5 Boston
6 Paris
7 Washington
8 LosAngeles
9 Orlando
10 London
;
run;

1 ACCEPTED SOLUTION

Accepted Solutions
LarryWorley
Fluorite | Level 6

Then adding distinct  and order by clause to PGStats second guess will get you this result.

proc sql;

 

create table pairs2 as

select distinct A.city as city1, B.city as city2

from places as A inner join places as B

on A.city < B.city

order by a.city, b.city

select * from pairs2;

quit;

View solution in original post

6 REPLIES 6
LarryWorley
Fluorite | Level 6

Tal,

Your requirements are very sketchy here, so I am making some assumptions.  What about:

proc sql ;

  create table paired_cities as

  select distinct city

           ,min(pid) as small_pid

          ,max(pid) as large_pid

    from places

   group by city

   having count(*) = 2
  ;

quit ;

DBailey
Lapis Lazuli | Level 10

you can easily generate pairs this way:

proc sql;

create table pairs as

select

     t1.pid as Pid_1,

     t1.city as City_1,

     t2.pid as PId_2,

     t2.city as City_2

from

     places p1, places p2

where

     p1.pid ne p2.pid;

quit;

One issue might be that this approach includes both LosAngeles, Orlando and Orlando, LosAngeles.  You can avoid this by:

proc sql;

create table unique_paris as

select

     t1.pid as PId_1,

     t1.city as City_1,

     t2.pid as PId_2,

     t2.city as City_2

from

     places t1, places t2

where

     t2.pid > t1.pid;

quit;

PGStats
Opal | Level 21

What do you want? Pairs of cities, pairs of PIDs, pairs of city-PID combinations.

Let me join the guessing game! Here are two more guesses as to what you may want:

proc sql;

create table pairs1 as

select city, min(pid) as pid1, max(pid) as pid2 from places

group by city having range(pid) > 0;

select * from pairs1;

create table pairs2 as

select A.city as city1, B.city as city2

from places as A inner join places as B

on A.city < B.city; 

select * from pairs2;

quit;

Excuse me if I don't spend too much time on those answers, you obviously didn't spend much on the question...

PG

PG
Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

sorry guys i was not more specific

this is what i am supposed to get:

1 Boston London
2 Boston LosAngeles
3 Boston NewYork
4 Boston Orlando
5 Boston Paris
6 Boston Washington
7 London LosAngeles
8 London NewYork
9 London Orlando
10 London Paris
11 London Washington
12 LosAngeles NewYork
13 LosAngeles Orlando
14 LosAngeles Paris
15 LosAngeles Washington
16 NewYork Orlando
17 NewYork Paris
18 NewYork Washington
19 Orlando Paris
20 Orlando Washington
21 Paris Washington

LarryWorley
Fluorite | Level 6

Then adding distinct  and order by clause to PGStats second guess will get you this result.

proc sql;

 

create table pairs2 as

select distinct A.city as city1, B.city as city2

from places as A inner join places as B

on A.city < B.city

order by a.city, b.city

select * from pairs2;

quit;

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

sweet.

Thanks Larry,

thanks guys for your time and efforts

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
  • 6 replies
  • 630 views
  • 1 like
  • 4 in conversation