Date:         Tue, 25 Mar 2003 18:26:20 +0100
Reply-To:     Michael.Eckhardt@T-SYSTEMS.COM
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Michael.Eckhardt@T-SYSTEMS.COM
Subject:      AW: help with an occurs in Proc Sql

Arto,

SELECT
        *
      , COUNT (*)
  FROM mylib."TABLE_OF_CARS"
 WHERE city = 'chicago' AND mileage > 25


... will produce exactly what you were asking for while I'm not sure if it is what you wanted ...

So if you have 10000 cars with mileage > 25 in your table you get 10000 lines with just an additional column _TEMG001 containing the distinct value 10000 across all rows (?).

btw: Unless you can be absolutely positive about column CITY containing only lower cases you're better off with looking for LOWCASE(city) = "chicago";

Mit freundlichen Gru?en
Best regards

Michael Eckhardt
Berater Business Intelligence
T-Systems
Systems Integration
Geschaftsstelle Darmstadt/FDL-Mitte
Frankfurter Str. 27

65760 Eschborn
Tel.  (06196) 961-540
Fax   (0711)  120303-014499
Mobil (0171)  5762151

E-mail: Michael.Eckhardt@t-systems.com
Internet: http://www.t-systems.de


"Unfortunately, the current generation of mail programs do not have checkers to see if the sender knows what he is talking about"
(A. S. Tanenbaum)




-----Ursprungliche Nachricht-----
Von: Arto Raiskio [mailto:arto@RAISKIO.COM]
Gesendet: Dienstag, 25. Marz 2003 18:07
An: SAS-L@LISTSERV.UGA.EDU
Betreff: help with an occurs in Proc Sql


could someone post a sample syntax for displaying in Proc Sql the counter
for a given value exceeding a limit?

for example. if my SQL Database contains cars and the city is 'chicago' and
I want only cars with gasmileage over 25 per gallon

libname mylib odbc noprompt="driver=(SQL
Server);server=xx,uid=xx;pwd=xx;database=xxx";
proc sql dquote=ansi;
 create table mytable as select *
     from mylib."the_table_of_cars"
  where (city = 'chicago' AND mileage > 25)
;

now add to that a counter for how many cars, ie. something like

 create table mytable as select *, (select count(*) mileage > 25)
     from mylib."the_table_of_cars"
  where (city = 'chicago' AND mileage > 25)
;

help?