Help using Base SAS procedures

proc sql : error in table creation

Reply
Contributor spg
Contributor
Posts: 61

proc sql : error in table creation

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!
Regular Contributor
Posts: 171

Re: proc sql : error in table creation

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.
Regular Contributor
Posts: 171

Re: proc sql : error in table creation

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;
Contributor spg
Contributor
Posts: 61

Re: proc sql : error in table creation

thanks much polingjw! the code was very helpful and i could also modify it to include more items of interest.
Super User
Posts: 9,676

Re: proc sql : error in table creation

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
Contributor spg
Contributor
Posts: 61

Re: proc sql : error in table creation

Thanks Ksharp for the useful tip!
Ask a Question
Discussion stats
  • 5 replies
  • 120 views
  • 0 likes
  • 3 in conversation