Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- Keep the same order in when statement

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 10-06-2020 08:36 AM
(971 views)

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 |

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

7 REPLIES 7

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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?

--

Paige Miller

Paige Miller

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

How are you getting this table?

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

--

Paige Miller

Paige Miller

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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 |

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;

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;

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. **Registration is now open through August 30th**. Visit the SAS Hackathon homepage.

What is Bayesian Analysis?

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.