Sorting is a vital course of when working with knowledge. It lets you arrange the info to make it simpler to work with and perceive. There are a number of methods to kind knowledge, however probably the most widespread strategies is SAS Proc SQL Order By.
Proc SQL is a robust instrument that can be utilized to kind knowledge in numerous methods. This text will present you tips on how to kind knowledge utilizing Proc SQL. We may also present some examples that will help you higher perceive proc SQL types knowledge.
Sorting by Column
The next instance selects the identify and their age from the sashelp.class desk and orders the outcomes by age:
proc sql outobs=5;
choose Identify, age from sashelp.class order by age;
stop;
Sorting by A number of Columns
You may kind by a couple of column by specifying the column names, separated by commas, within the ORDER BY clause. The next instance types the sashelp.class desk by two columns, intercourse and identify:
proc sql outobs=12;
title 'CLASS, Sorted by intercourse and Identify';
choose Identify, intercourse
from sashelp.class
order by intercourse, Identify;
Sorting by Calculated Column
You may kind by a calculated column by specifying its alias within the ORDER BY clause. The next instance calculates BMI after which performs a kind on the calculated BMI column:
proc sql outobs=10;
title 'Class dataset sorted by BMI';
choose Identify, intercourse, weight label="Weight(kgs)", peak label="Peak(cms)",
spherical(divide((weight*703), peak**2),0.01) as BMI from sashelp.class order
by bmi desc;
Sorting by Column Place
You may kind by any column throughout the SELECT clause by specifying its numerical place. By specifying a place as a substitute of a reputation, you’ll be able to kind by a calculated column with no alias.
The next instance doesn’t assign an alias to the calculated BMI column. As an alternative of referring to the column identify within the ORDER BY clause, it refers back to the place of the calculated column within the SELECT clause:
proc sql outobs=10;
title 'Class dataset sorted by BMI';
choose Identify, intercourse, weight label="Weight(kgs)", peak label="Peak(cms)",
spherical(divide((weight*703), peak**2),0.01) label='BMI' from sashelp.class order by 5
desc;
Sorting by Columns That Are Not Chosen
Question outcomes may be sorted by columns not included within the question. For instance, the next question returns all rows of the sashelp.class desk sorted by age, regardless of not together with the age column:
proc sql outobs=12;
title 'CLASS, Sorted by age and Identify';
choose Identify, intercourse from sashelp.class order by age, Identify;
Specifying a Completely different Sorting Sequence
With SORTSEQ=, PROC SQL specifies the order wherein a question is sorted when the ORDER BY clause is current. If you wish to kind in a different way out of your working system’s default, use this feature.
This instance demonstrates the performance of SORTSEQ with PROC SQL:
choices sortseq=reverse;
proc sql outobs=10;
choose * from sashelp.class order by identify;
stop;
Sorting Columns That Include Lacking Values
PROC SQL types nulls, or lacking values, earlier than character or numeric knowledge. Subsequently, lacking values seem first within the question outcomes while you specify ascending order.
proc surveyselect knowledge=sashelp.coronary heart out=pattern methodology=srs sampsize=10;
run;
proc sql;
choose standing, deathcause from pattern order by deathcause;
Conclusion
This text confirmed tips on how to kind knowledge utilizing Proc SQL. To do that, it’s essential to use the ORDER BY clause within the SELECT assertion. You may as well use the DESC choice to kind in descending order.
