Date:         Thu, 5 Jan 2006 15:08:19 -0500
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: SQL Question
Comments: To: Mark Wise <SASTutor@YAHOO.COM>
Content-Type: text/plain; charset="windows-1255"

Mark:
An alias in a SELECT list has to have the alias operator AS. Try
proc sql;
 select distinct(cust) as cust,
 count(cust) as count, group1
 from x
 group by group1;
quit;

<Note that you should specify 'quit', not 'run'.>

This query yields what you have labelled 'desired output'.

Perhaps the output listing that you show represents what the query produced and not what you want. Guessing what you want (always dangerous), you might want to test

proc sql;
select group1,count(distinct cust) as nCustomers
from x
group by group1;
quit;

The results of the second query make more sense to me: a count of customers within each group. Where customers overlap groups, the sum of the counts will exceed the total number of customers, but that makes sense in context.
Sig

-----Original Message-----
From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu] On Behalf Of Mark Wise
Sent: Thursday, January 05, 2006 11:39 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: SQL Question


Thanks for the response.  I do need a count, but I am not getting the correct answer.


data x;
input pol cust $ prem group1 $;
datalines;;
1    a 1 x
1    a 1 x
2    b 1 x
3    c 1 x
4    d 1 x
4    d 1 x
4    d 1 x
4    d 1 x
4    d 1 y
4    d 1 y
4    d 1 z
;
run;

proc sql;
select distinct(cust) as cust,
count(cust) as count,
group1
from x
group by group1;
run;

This doesnt give me the desired output

cust         count  group1
ャャャャャャャャャャャャャャ
a                8  x
b                8  x
c                8  x
d                1  z
d                2  y
d                8  x