topic Re: proc sql : error in table creation in SAS Procedures

proc sql : error in table creation

spg — Tue, 07 Sep 2010 15:37:32 GMT

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!

Re: proc sql : error in table creation

polingjw — Tue, 07 Sep 2010 16:24:55 GMT

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.

Re: proc sql : error in table creation

polingjw — Tue, 07 Sep 2010 17:33:58 GMT

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;

Re: proc sql : error in table creation

spg — Wed, 08 Sep 2010 03:13:12 GMT

thanks much polingjw! the code was very helpful and i could also modify it to include more items of interest.

Re: proc sql : error in table creation

Ksharp — Wed, 08 Sep 2010 05:24:07 GMT

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

Re: proc sql : error in table creation

spg — Thu, 09 Sep 2010 21:52:13 GMT

Thanks Ksharp for the useful tip!