road name in all the city

[soham_sas]

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  

city	road
delhi	a
mumbai	a
chennai	a
bangalore	a

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;

[Criptic]

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 · Accepted solution]

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]

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;