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 |
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.
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?
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
How are you getting this table?
Is it from a PROC? If so, be specific.
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 |
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.