need those animal which are not in city

[aanan1417]

animal         city 

dog         delhi

cat           delhi

lion          delhi

bear       mumbai

cat           mumbai

mouse     mumbai

dog          chennai

mouse      chennai

bear          chennai

output 

animal         city 

delhi            bear

delhi            mouse

mumbai       dog

mumbai       lion

chennai       cat

chennai       lion

[aanan1417]

data a;
input animal $ city $;
datalines;
dog delhi
cat delhi
lion delhi
bear mumbai
cat mumbai
mouse mumbai
dog chennai
mouse chennai
bear chennai
;
run;

Output

data b;
input animal $ city $;
datalines;
bear delhi
mouse delhi
dog mumbai
lion mumbai
cat chennai
lion chennai
;
run;

[PaigeMiller]

Do not post the same question twice. All replies should go to your other thread at https://communities.sas.com/t5/SAS-Programming/need-those-animal-which-are-not-in-city/td-p/773036/

[maguiremq]

I really love the PROC FREQ `sparse` option for these. You can do it in PROC SQL, too, but I'm not the best PROC SQL programmer.

data have;
input animal :$5. city :$7.;
datalines;
dog delhi
cat delhi
lion delhi
bear mumbai
cat mumbai
mouse mumbai
dog chennai
mouse chennai
bear chennai
;
run;

proc freq
	data = have;
		tables animal * city / list sparse out = want (keep = animal city count where = (count = 0));
run;

Obs 	animal 	city 	COUNT
1 	bear 	delhi 	0
2 	cat 	chennai 	0
3 	dog 	mumbai 	0
4 	lion 	chennai 	0
5 	lion 	mumbai 	0
6 	mouse 	delhi 	0

[HB]

@maguiremq   I wouldn't worry about not seeing the SQL solution.  It sure wasn't readily apparent to me!   After futzing with it, I decided if you made a cartesian list of distinct cities and animals you should be able to left join that to the data to see what is missing.

proc sql;
	create table animals as
	select distinct animal
	from have;

	create table cities as
	select distinct city
	from have;

	create table cartesianlist as 
	select a.city, b.animal 
	from cities a, animals b;

	select a.city, a.animal
	from cartesianlist a
	left join have b on a.city = b.city and a.animal = b.animal 
	where b.animal is null
	order by a.city, a.animal;
quit;

Output is

city	animal
chennai	cat
chennai	lion
delhi	bear
delhi	mouse
mumbai	dog
mumbai	lion

I like the sparse thing better.  Never used that before. 

Edit:  The web says "the SPARSE option provides 'all possible combinations of
levels of the variables in the table, even when some
combination levels do not occur in the data.'

All possible combinations is essentially a Cartesian join.  So that makes sense.   

[Ksharp · Accepted solution]

data have;
input animal :$5. city :$7.;
datalines;
dog delhi
cat delhi
lion delhi
bear mumbai
cat mumbai
mouse mumbai
dog chennai
mouse chennai
bear chennai
;
run;
proc sql;
create table want as
select a.*
 from (select * from (select distinct animal from have),(select distinct city from have)) as a 
 natural left join have as b
 where b.animal is missing;
quit;