Prev: Error in Importing
Next: WARNING: End of file
From: Hari on 25 Aug 2007 06:07
Hi,

I want to find count of distinct records for a dataset based on some
conditions and used the syntax "Select count(distinct *)" but SAS
doesnt seem to like it?

Proc SQL;
16 Select count(distinct *) as CntDisSalesTransactions
17 from ProjRaw.Sales (keep = ID PURCHASE_DATE SKU)
18 where ID not in (select ID from
ProjTmp.IDinSales_NotInDiscounts)
19 and PURCHASE_DATE lt "01JUL07"d;
ERROR: * used in an illegal position.

I can overcome this by using the below form, but it seems too unweildy/
unnecessary.

Proc SQL:
Select count(*) as CntDisSalesTransactions
from (Select distinct *
from ProjRaw.Sales (keep = ID PURCHASE_DATE SKU)
where ID not in (select ID from
ProjTmp.IDinSales_NotInDiscounts)
and PURCHASE_DATE lt "01JUL07"d);
Quit;

Any ideas?

hp

From: zbig on 25 Aug 2007 06:21
Hari wrote:
>
> Any ideas?
>

maybe somethin like

select count distinct (put(ID,10.) || put( PURCHASE_DATE, date9.) || SKU)
.....

I would also consider replacing
"where ID not in (select something from another table)"
with a join

regards
zbig
From: "Howard Schreier <hs AT dc-sug DOT org>" on 25 Aug 2007 07:55
On Sat, 25 Aug 2007 03:07:03 -0700, Hari <excel_hari(a)YAHOO.COM> wrote:

>Hi,
>
>I want to find count of distinct records for a dataset based on some
>conditions and used the syntax "Select count(distinct *)" but SAS
>doesnt seem to like it?
>
>Proc SQL;
>16 Select count(distinct *) as CntDisSalesTransactions
>17 from ProjRaw.Sales (keep = ID PURCHASE_DATE SKU)
>18 where ID not in (select ID from
>ProjTmp.IDinSales_NotInDiscounts)
>19 and PURCHASE_DATE lt "01JUL07"d;
>ERROR: * used in an illegal position.

COUNT is a summary function. A summary function takes exactly one argument.

>
>I can overcome this by using the below form, but it seems too unweildy/
>unnecessary.
>
>Proc SQL:
>Select count(*) as CntDisSalesTransactions
>from (Select distinct *
> from ProjRaw.Sales (keep = ID PURCHASE_DATE SKU)
> where ID not in (select ID from
>ProjTmp.IDinSales_NotInDiscounts)
> and PURCHASE_DATE lt "01JUL07"d);
>Quit;
>
>Any ideas?
>
>hp
From: Jake Bee on 28 Aug 2007 08:48
As set up I don't think you can count * into an "[as var]". But perhaps as
the last distinct (count(*)) as temp_count (attached) you can achieve your
result. But I think your interested in the id and that should be
sufficient.


dm 'log' clear;
dm 'out' clear;

options notes spool source2;

data sales;
format purchase_date date9.;
id=1; purchase_date='29May07'd; sku=20071; output;
id=2; purchase_date='29Jun06'd; sku=20061; output;
id=2; purchase_date='27Jun07'd; sku=20072; output;
id=3; purchase_date='01Jan06'd; sku=20062; output;
id=4; purchase_date='03Mar07'd; sku=20073; output;
id=5; purchase_date='03Jul07'd; sku=20074; output;
run;

proc sort data=sales;
by id purchase_date;
run;

proc print;
run;

data IDinSales_NotInDiscounts;
id=1; output;
id=3; output;
run;


proc sql NOEXEC feedback undo_policy=none;
create table distinct_sales_not_discounted
as select distinct id, purchase_date, sku
from sales
where id ^in (select distinct id from IDinSales_NotInDiscounts);
quit;


proc print data=_last_;
run cancel;

proc sql feedback undo_policy=none;
create table sales_review
as Select count (DISTINCT a.ID) as CntDisSalesTransactions
from Sales(keep = ID PURCHASE_DATE SKU) as a
where a.ID ^in (select distinct ID from IDinSales_NotInDiscounts)
and a.PURCHASE_DATE < "01Jul07"d;
quit;

proc print data=_last_;
run;

*--- last example distinct (count (*)) ---> number of records ---*;

proc sql feedback undo_policy=none;
create table counts
as select distinct (count (*)) as temp_count
from sales;
quit;



On 8/28/07, ben.powell(a)cla.co.uk <ben.powell(a)cla.co.uk> wrote:
>
> Here is another variation that may help explain what the great man Codd
> was
> thinking (should not produce an error):
>
> proc sql;
> create table temp as
> select distinct var1, var2, var3, count(*)
> from records;
> quit;
>
> HTH.
>
 | 
Pages: 1
Prev: Error in Importing
Next: WARNING: End of file