I have following data
Number | F | Count | Name |
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 |
I want to filter it on the basis of subcategories Count >=2 & Name = 'WWF' and I need the whole group in the output.
Number | F | Count | Name |
1234 | 2 | 1 | OBC |
1234 | 2 | 2 | WWF |
3456 | 3 | 1 | ABC |
3456 | 3 | 2 | DEF |
3456 | 3 | 3 | WWF |
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.
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;
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.
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;
I want to learn how this count in where clause works.
What I understand Count(*) ( Aggregate function ) works with group by and having clause.
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;
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
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.