topic Re: Keep the same order in when statement in SAS Procedures

Keep the same order in when statement

mazouz — Tue, 06 Oct 2020 12:36:05 GMT

PROC SQL;
CREATE TABLE WANT AS 
   SELECT 	
CASE 
WHEN A=0 THEN "FIRST"
WHEN B=0 THEN "SECOND"
WHEN C=0 THEN "THIRD"
ELSE "LAST" 
END AS ANALYSIS
FROM HAVE;
QUIT;

Hello Guys, I want to keep the same ordre in my "want" table

FIRST

SECONDE

THIRD

LAST

But the problem is in case statement SQL order by alphabetic like that:

FIRST

LAST

SECONDE

THIRD

Re: Keep the same order in when statement

PaigeMiller — Tue, 06 Oct 2020 12:42:53 GMT

Yes, the default sort order of character variables is alphabetic.

But I don't understand, when you show the orders, this is an order in a SAS DATA set? The order then depends on the rows of the data set WANT and not the values of variable ANALYSIS. So its really not clear what you are asking for here. How do you get that final table?

Re: Keep the same order in when statement

mazouz — Tue, 06 Oct 2020 12:46:14 GMT

My question is if i can modify the default sort order of character variables

the result I want is :

ANALYSIS	PERCENT
FIRST	%
SECONDE	%
THIRD	%
LAST	%

dont care about Percent variable it's just an exemple. but my object is to keep order Fisrt second third last not by alphabetic

Re: Keep the same order in when statement

PaigeMiller — Tue, 06 Oct 2020 12:47:09 GMT

How are you getting this table?

Is it from a PROC? If so, be specific.

Re: Keep the same order in when statement

Kurt_Bremser — Tue, 06 Oct 2020 13:09:30 GMT

I guess you see that "wrong" order when you later analyze your ANALYSIS variable, e.g. with PROC FREQ. Those outputs will be ordered alphabetically.

If you create an underlying order through a format:

proc format;
value myvals
  1 = 'First'
  2 = 'Second'
  3 = 'Third'
  99 = 'Last'
;
run;

and change the WHEN clause to

case
  when A = 0 then 1
  when B = 0 then 2"
  when C = 0 then 3
  else 99 
end as analysis format=myvals.

then you can use options like

proc freq data=have order=internal;

later to keep the wanted order in the output.

Re: Keep the same order in when statement

mazouz — Tue, 06 Oct 2020 13:11:36 GMT

proc sql ;
create table WANT as
select case when x<5 then "first"
when x<50 then "second"
when x<150 then "third"
else "last"
end as analysis,sum(n) as SUM
from HAVE
group by analysis;
quit;

HAVE:

x	n
0,6	1
1	2
10	70
25	120
60	60
500	3
510	1

Re: Keep the same order in when statement

Kurt_Bremser — Tue, 06 Oct 2020 13:26:05 GMT

Now that's pretty obvious:

@mazouz wrote:

proc sql ;
create table WANT as
select case when x<5 then "first"
when x<50 then "second"
when x<150 then "third"
else "last"
end as analysis,sum(n) as SUM
from HAVE
group by analysis;
quit;

You create a character variable and use it in a GROUP BY, and that means alphabetic sorting.

By using the format as I suggested in my previous post, the order in the output will be as you want it:

data have;
input x n;
datalines;
0.6 1
1 2
10  70
25  120
60  60
500 3
510 1
;

proc format;
value myvals
  1 = 'First'
  2 = 'Second'
  3 = 'Third'
  99 = 'Last'
;
run;

proc sql;
create table WANT as
  select
    case
      when x<5 then 1
      when x<50 then 2
      when x<150 then 3
      else 99
    end as analysis format=myvals.,
    sum(n) as SUM
  from HAVE
  group by analysis
;
quit;

Re: Keep the same order in when statement

Ksharp — Wed, 07 Oct 2020 12:28:21 GMT

Or could padding some white blanks before it. and order by it .

PROC SQL;
CREATE TABLE WANT AS
SELECT
CASE
WHEN A=0 THEN " FIRST"
WHEN B=0 THEN " SECOND"
WHEN C=0 THEN " THIRD"
ELSE "LAST"
END AS ANALYSIS length=20
FROM HAVE
order by analysis ;
QUIT;