I have a big data, and i wanted to select a few obs of a variable. How can I do that with big data?
For example: I have a data similar to the table below
class |
ID |
D4 |
1 |
A6 |
2 |
F9 |
3 |
F9 |
4 |
C5 |
5 |
A6 |
6 |
R10 |
7 |
R10 |
8 |
R10 |
9 |
I want the output to look like this
class |
ID |
A6 |
2 |
F9 |
3 |
F9 |
4 |
A6 |
6 |
R10 |
7 |
R10 |
8 |
R10 |
9 |
If you're only looking to extract the classes that appear multiple times, you could use something like this:
proc sql;
create table output as
select
a.class, a.id
from
work.test a
inner join
(
select distinct
b.class,
count(b.class) as class_count
from
work.test b
group by
b.class
having
calculated class_count > 1
) c
on
a.class = c.class
order by
a.class, a.id
;
What is the logic here?
@hjjijkkl wrote:
I have a big data and i am trying to create a table with selected obs of a variable. for example in the table I provided, in the variable class, I was trying to get only the obs identified as A6, F9 and R10 only.
data want;
set have;
where class in ('A6' 'F9' 'R10');
run;
should do it.
If you're only looking to extract the classes that appear multiple times, you could use something like this:
proc sql;
create table output as
select
a.class, a.id
from
work.test a
inner join
(
select distinct
b.class,
count(b.class) as class_count
from
work.test b
group by
b.class
having
calculated class_count > 1
) c
on
a.class = c.class
order by
a.class, a.id
;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.