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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.