topic Proc Summary-Order of raws in output in SAS Programming

Proc Summary-Order of raws in output

Ronein — Wed, 10 Jun 2020 03:53:01 GMT

Hello

I want to ask what is the default way that SAS sort the rows (of class variable) in proc summary.

for example:

in this example order of rows is:

10-20K
100K+
20-50K
5-10K
50-100K

May anyone explain why the order is like that?


proc format;
value FF2T
0 <- 1000='0-1K'
1000 <- 5000='1-5K'
5000 <- 10000='5-10K'
10000 <- 20000='10-20K'
20000 <- 50000='20-50K'
50000 <- 100000='50-100K'
100000 <- high='100K+';
;
run;
Data Sedan_cars;
set sashelp.cars(where=(Type='Sedan'));
Invoice_=put(Invoice,FF2T.);
run;

proc summary data=Sedan_cars  nway missing ;
class Type Invoice_ ;
var Invoice;
output out=summaryTbl(drop=_type_) sum= mean= /autoname;
run;

Re: Proc Summary-Order of raws in output

smantha — Wed, 10 Jun 2020 04:04:37 GMT

your format is a character and imagine this is from a dictionary then the order will be as follows and hence the discrepancy

100

110

300

etc.

in order for things to be consistent make them all two or three positional digits by padding with leading zeros. Good luck

Re: Proc Summary-Order of raws in output

Kurt_Bremser — Wed, 10 Jun 2020 04:41:12 GMT

Follow Maxim 1 and Read the Documentation, in this case, of the CLASS Statement. Hint: look for "Order".

Re: Proc Summary-Order of raws in output

PaigeMiller — Wed, 10 Jun 2020 11:41:32 GMT

This was all explained in a thread you started two weeks ago

https://communities.sas.com/t5/SAS-Programming/proc-summary-order-of-categories-is-not-as-I-want/m-p/651326#M195417

If you create INVOICE_ as a character variable, it will sort as character variable (which sorts alphabetically), where strings that begin with 1 come before strings that begin as 2. You should not create INVOICE_ as a character variable and then try to jump through hoops to get it to sort as character.

If you create INVOICE_ as a numeric variable, it will sort as numeric, and sort in the desired order. My reply in that thread shows you exactly how to do it.

Summary: character variables sort alphabetically. Numeric variables will sort numerically. In this case, you want it to be a numeric variable.

Re: Proc Summary-Order of raws in output

ballardw — Wed, 10 Jun 2020 14:18:55 GMT

You might try making a format that sorts in the order that you want things, such as:

proc format;
value FF2T
0      <- 1000  ='  0-1K'
1000   <- 5000  ='  1-5K'
5000   <- 10000 ='  5-10K'
10000  <- 20000 =' 10-20K'
20000  <- 50000 =' 20-50K'
50000  <- 100000=' 50-100K'
100000 <- high  ='100K+';
;

Especially if you are going to insist on making character variables. If you use the numeric value directly then you can usually use the internal value to control order. But that choice is taken away with character variables, or at least it will behave quite differently.

Notice that nicely formatted code would show the "sort order" of the character values this way. The space will sort before 1, 2, 3 etc.