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

HI , 

i have a data set like below , i have to check which road is there in all the city , for example "a" is there in all the city so we want the output to be like  

 

cityroad
delhia
mumbaia
chennaia
bangalorea

 

the data is like below 

 

data dsn;
infile cards dlm='09'x;
input city$ road$;
cards;
delhi a
delhi b
delhi c
mumbai d
mumbai a
chennai e
chennai f
chennai a
chennai g
bangalore i
bangalore j
bangalore k
bangalore a
;
run;

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
data dsn;
infile cards;
input city :$20. road $;
cards;
delhi a
delhi b
delhi c
mumbai d
mumbai a
chennai e
chennai f
chennai a
chennai g
bangalore i
bangalore j
bangalore k
bangalore a
;
run;
proc sql;
select *
 from dsn
  group by road
    having count(distinct city) eq (select count(distinct city) from dsn);
quit;

View solution in original post

3 REPLIES 3
Criptic
Lapis Lazuli | Level 10

Just try a order by or where road eq 'x'

data dsn;
infile cards;
input city$ road$;
cards;
delhi a
delhi b
delhi c
mumbai d
mumbai a
chennai e
chennai f
chennai a
chennai g
bangalore i
bangalore j
bangalore k
bangalore a
;
run;

proc sql;
create table dsn_ordered as
select * from dsn
order by road, city;
quit;
Ksharp
Super User
data dsn;
infile cards;
input city :$20. road $;
cards;
delhi a
delhi b
delhi c
mumbai d
mumbai a
chennai e
chennai f
chennai a
chennai g
bangalore i
bangalore j
bangalore k
bangalore a
;
run;
proc sql;
select *
 from dsn
  group by road
    having count(distinct city) eq (select count(distinct city) from dsn);
quit;
rvsidhu035
Quartz | Level 8
data dsn;
input city$ road$;
cards;
delhi a
delhi b
delhi c
mumbai d
mumbai a
chennai e
chennai f
chennai a
chennai g
bangalore i
bangalore j
bangalore k
bangalore a
;
run;
proc freq data=dsn;
table road/out=dsn1;run;
proc sort data=dsn1;by road;run;
proc sort data=dsn;by road;run;
data merg;
merge dsn dsn1;by road;run;
data want(keep=road city);
set merg;
if count=4;run;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1540 views
  • 0 likes
  • 4 in conversation