BookmarkSubscribeRSS Feed
spg
Obsidian | Level 7 spg
Obsidian | Level 7
Hi,

I have a data set "test" from which I want to select only those observations for which the product column is 6 AND 3 for every group of ID. The desired output table is test1 below.

test

ID product
-----------------------
101 6
101 3
101 5
101 3
-----------------------
102 2
102 3
102 6
102 6
-----------------------
103 6
103 6
103 6
103 6


test1

ID product
----------------------
101 6
101 3
101 3
----------------------
102 3
102 6
102 6




I tried using the following program but it fails to group by the IDs and is returning any observation that is a 6 or a 3. Can anybody please point what's wrong?

proc sql;
create table test1 as
select *
from test
where product in (6 3)
group by hhid;
quit;

Thanks!
5 REPLIES 5
polingjw
Quartz | Level 8
The group by clause is used when summary statistics are requested, such as the min, max, or average. Since you are not requesting any summary statistics, the group by clause has no impact. You could use an order by clause instead and subsequently print the results by hhid.
polingjw
Quartz | Level 8
I think that I might have misunderstood your post the first time I read it. You should consider using a subquery to find all the IDs which have values of both 6 and 3. For example:

proc sql;
create table test1 as
select *
from test
where product in (6 3) and hhid in
(select hhid
from test
where product = 6
intersect
select hhid
from test where product=3);
quit;
spg
Obsidian | Level 7 spg
Obsidian | Level 7
thanks much polingjw! the code was very helpful and i could also modify it to include more items of interest.
Ksharp
Super User
Hi.
you need a 'having' statement to fliter the group.
try this.

[pre]
data patrick;
input id $ product;
datalines;
101 6
101 3
101 5
101 3
102 2
102 3
102 6
102 6
103 6
103 6
103 6
103 6
;
run;

proc sql;
create table test1 as
select *
from patrick
where product in (6 3)
group by id
having count( distinct product) ge 2;
run;
quit;
proc print;
run;
[/pre]


Ksharp
spg
Obsidian | Level 7 spg
Obsidian | Level 7
Thanks Ksharp for the useful tip!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 845 views
  • 0 likes
  • 3 in conversation