FJ,

Well, it's not doing what you want, but it IS doing what you're telling it to do. Population (your formatted value) is a character variable, and so PROC SQL is putting the character strings in order, rather than the original numbers. You need a "representative" numeric value in each group to do the ordering -- I used MIN, but other functions such as MAX would also do the trick.

Mike Rhoads RhoadsM1@Westat.com

proc format; value pop1_f low-499 = "<500" 500-9999 = "500-9,999" 10000-high = ">10,000"; run;

data inventory; input system_population @@; system = _n_; datalines; 11000 250 200 15000 700 10000 800 25000 6000 run;

proc sql; select count(system) into :num from inventory; select Population, Number, Percent from ( select put(system_population, pop1_f.) as Population, count(put(system_population, pop1_f.)) as Number, round((count(system_population)/&num)*100,0.1) as Percent, min(system_population) as OrderVar from inventory group by calculated Population ) order by OrderVar; quit;

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of fakyjunky@YAHOO.COM Sent: Monday, April 06, 2009 9:15 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Proc SQL not ordering formatted data correctly

I can't get proc sql to order my formatted data correctly. Below is the code for proc format and proc sql to count the number of population categories. The output orders numbers first, and characters like "<" last. This does not happen in Proc Freq, where the resulting output categories are ordered correctly. How can this be fixed in Proc SQL?

Thanks, FJ

P.S. I'm using Proc SQL as part of automating a process, so I'm trying not to use Proc Freq if I can.

Code:

proc format; value pop1_f low-499 = "<500" 500-9999 = "500-9,999" 10000-high = ">10,000"; run;

proc sql; select count(system) into :num from inventory; select put(system_population, pop1_f.) as Population, count(put(system_population, pop1_f.)) as Number, round((count(system_population)/&num)*100,0.1) as Percent from inventory group by calculated Population order by calculated Population; quit;

RESULT:

Population Number Percent 500-9,999 763 24.3 <500 1947 62.1 >10,000 426 13.6

QUESTION? How can I get it to do the right order:

Population Number Percent <500 1947 62.1 500-9,999 763 24.3 >10,000 426 13.6