DATA Step, Macro, Functions and more

Filter on the basis of subcategories

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

Filter on the basis of subcategories

 

I have following data

 

NumberFCountName
123421OBC
123422WWF
345631ABC
345632DEF
345633WWF
566621XYZ
566622ABC
788831WWF
788832XYZ
788833ABC

 

I want to filter it on the basis of subcategories Count >=2 & Name = 'WWF' and I need the whole group in the output. 

 

NumberFCountName
123421OBC
123422WWF
345631ABC
345632DEF
345633WWF

 

I wrote the following codes

 

data test;
set want;
by Number;
if count >=2 and Name = 'AWF';
run;

 

However with this I am not getting whole group in output rather I am getting only the records that are matching the given criteria.

Please guide.


Accepted Solutions
Solution
‎09-11-2016 08:18 PM
Super User
Posts: 17,868

Re: Filter on the basis of subcategories

This is easier in SQL. I'm assuming count refers to the variable count not an aggregation. 

 

Proc SQL;
Create table want as
Select *
From have
where number in (select distinct number from have where name="WWF" and count>=2);
Quit;

View solution in original post


All Replies
Regular Contributor
Posts: 242

Re: Filter on the basis of subcategories

data HAVE;
input Number F Count Name $;
datalines;
1234 2 1 OBC
1234 2 2 WWF
3456 3 1 ABC
3456 3 2 DEF
3456 3 3 WWF
5666 2 1 XYZ
5666 2 2 ABC
7888 3 1 WWF
7888 3 2 XYZ
7888 3 3 ABC
;
proc sql;
create table Name_WWF as
select distinct Number 
from HAVE
WHERE NAME='WWF';

create table want as
select *
from have as outer 
where 
2 <= (select count(*)
from have as inner
where Number in (select number from Name_WWF )
and outer.number=inner.number 
group by number
);
QUIT;

first I found out Number that have 'WWF' and then I have used corelated query to get the desired result.

Solution
‎09-11-2016 08:18 PM
Super User
Posts: 17,868

Re: Filter on the basis of subcategories

This is easier in SQL. I'm assuming count refers to the variable count not an aggregation. 

 

Proc SQL;
Create table want as
Select *
From have
where number in (select distinct number from have where name="WWF" and count>=2);
Quit;
Regular Contributor
Posts: 242

Re: Filter on the basis of subcategories

@Reeza

I want to learn how this count in where clause works.

 

What I understand Count(*) ( Aggregate function )  works with group by and having clause. 

Super User
Posts: 9,682

Re: Filter on the basis of subcategories



data have;
infile cards expandtabs truncover;
input Number	F	Count	Name $;
cards;
1234	2	1	OBC
1234	2	2	WWF
3456	3	1	ABC
3456	3	2	DEF
3456	3	3	WWF
5666	2	1	XYZ
5666	2	2	ABC
7888	3	1	WWF
7888	3	2	XYZ
7888	3	3	ABC
;
run;
data want;
 do until(last.number);
  set have;
  by number;
 end;
 _c=count;_n=name;
 do until(last.number);
  set have;
  by number;
  if _c ge 2 and _n='WWF' then output;
 end;
 drop _:;
run;


Super User
Posts: 9,682

Re: Filter on the basis of subcategories

If data like the following ,what do you expect ?


Number	F	Count	Name
1234	2	1	OBC
1234	2	2	WWF
1234        2      3      ABC  <-----------------
3456	3	1	ABC
3456	3	2	DEF
3456	3	3	WWF
5666	2	1	XYZ
5666	2	2	ABC
7888	3	1	WWF
7888	3	2	XYZ
7888	3	3	ABC
 

Contributor
Posts: 71

Re: Filter on the basis of subcategories

@Ksharp

Actually F is 'Frequency', If there will be one more record of number '1234' then it will be like 

Number	F	Count	Name
1234	3	1	OBC
1234	3	2	WWF
1234    3       3    ABC

and in the output I would expect this record because 'WWF' existing at count = 2.

I need all the records of the group by Number where Name is 'WWF' with count=>2.

 

Thanks for all your support. 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 338 views
  • 3 likes
  • 4 in conversation