topic order of categories in summary table produced by proc sql in SAS Programming

order of categories in summary table produced by proc sql

Ronein — Wed, 21 Aug 2019 06:39:03 GMT

Hello

I have a problem.

I use CASE when statement to create a new variable called "IND".

As you can see I create different groups .

Later I will calculate summary statistics for each group.

I want that the order of the groups will be as it appear here:

'a1.Case1'

'a2.Case2'

'a3.Case3'

'a4.<Lower than -100K'

'a5.[-100,-50)'

and so on....

The problem is that the order is not as I want

case when (X=0 OR X=.) and W>0 then 'a1.Case1'
when (W=0 OR W=.) and X>0 then 'a2.Case2'
When (X=0 OR X=.) and (W=0 OR W=.) then 'a3.Case3'
when calculated Dif1<(-100000) then 'a4.<Lower than -100K'
when calculated Dif1>=(-100000) AND calculated Dif1<(-50000) then 'a5.[-100,-50)'
when calculated Dif1>=(-50000) AND calculated Dif1<(-40000) then 'a6.[-50,-40)'
when calculated Dif1>=(-40000) AND calculated Dif1<(-30000) then 'a7.[-40,-30)'
when calculated Dif1>=(-30000) AND calculated Dif1<(-20000) then 'a8.[-30,-20)'
when calculated Dif1>=(-20000) AND calculated Dif1<(-15000) then 'a9.[-20,-15)'
when calculated Dif1>=(-15000) AND calculated Dif1<(-10000) then 'a10.[-15,-10)'
when calculated Dif1>=(-10000) and calculated Dif1<(-5000) then 'a11.[-10,-5)'
when calculated Dif1>=(-5000) and calculated Dif1<(-1000) then 'a12.[-5,-1)'
when calculated Dif1>=(-1000) and calculated Dif1<0 then 'a13.[-1,0)'
end as Ind

PROC SQL;
create table Summary_tbl1 as
select Ind,
count(*) as no_customers format=comma18.,
sum( Dif1)/1000000 as Sum_Dif1_MLS format=comma18.
from RawData
group by Ind
order by Ind
;
QUIT;

Re: order of categories in summary table produced by proc sql

Kurt_Bremser — Wed, 21 Aug 2019 07:52:56 GMT

In the character collating sequence, "a10" comes after "a1" and before "a2". If you will have some two-digit numbers, the one-digit numbers need a leading zero.

Even better is to use a numeric value in a numeric variable (where 10 automatically follows 9) and apply a display format that contains your human-readable texts.

Re: order of categories in summary table produced by proc sql

Ronein — Wed, 21 Aug 2019 09:01:52 GMT

What is the recommended way to control the order of the groups?

Re: order of categories in summary table produced by proc sql

Kurt_Bremser — Wed, 21 Aug 2019 09:39:07 GMT

Both methods can be used, but personally I prefer to use a numeric value with a format. Especially if you do not want the leading zeroes in a report or a graphic meant for human (of the management variety) consumption.

Re: order of categories in summary table produced by proc sql

PaigeMiller — Wed, 21 Aug 2019 12:09:30 GMT

@Ronein wrote:

What is the recommended way to control the order of the groups?

In PROC REPORT you have several ways to control the order of groups, and you don't have to resort to forcing them to be sorted by adding a1 a2 etc to the label. You can have them sort by the underlying data value of variable DIF1. (This is one of many many many benefits of using PROC REPORT instead of PROC SQL to do reporting)