topic Re: need those animal which are not in city in SAS Programming

need those animal which are not in city

aanan1417 — Fri, 08 Oct 2021 13:50:00 GMT

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

come out those animal which are not in city

aanan1417 — Fri, 08 Oct 2021 14:06:06 GMT

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;

Re: need those animal which are not in city

maguiremq — Fri, 08 Oct 2021 14:09:38 GMT

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

Re: come out those animal which are not in city

PaigeMiller — Fri, 08 Oct 2021 14:13:00 GMT

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/

Re: need those animal which are not in city

HB — Fri, 08 Oct 2021 16:58:19 GMT

@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.

Re: need those animal which are not in city

Ksharp — Sun, 10 Oct 2021 10:40:55 GMT

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;