LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (March 2003, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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


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: Internet:

"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) ;


Back to: Top of message | Previous page | Main SAS-L page