• Print  |
  • Feedback  |

COMMUNITY

Sorting Variables More Easily with New ORDER= Option Values in the CONTENTS Procedure

A question that SAS customers have asked at various SAS Users Group meetings is how to sort column names with PROC CONTENTS to be able to more easily find particular columns. There are different methods for accomplishing this task. One method is to output the variable list from PROC CONTENTS to a data set and use the SORT procedure to sort the data set by the variable names. Another approach is to query the SQL procedure's dictionary table (DICTIONARY.COLUMNS), and then use an ORDER BY clause to sort the values by column name.

Both of these solutions are not intuitive because they require knowledge of the SAS Output Delivery System (ODS) or other procedures with which you might not be familiar. The ODS solution takes several PROC steps and knowledge of which ODS output object to write to a data set. The PROC SQL solution requires writing a SQL query and knowledge of PROC SQL dictionary tables. Thanks to suggestions from SAS customers, the following new values have been added to the ORDER=option (in addition to ORDER=VARNUM) in PROC CONTENTS:

The PROC CONTENTS syntax with the ORDER= values is as follows:

proc contents data=dsname   
              order=(casecollate | collate | ignorecase | varnum);  
run;

The next sections explain each ORDER= value in detail and provide output to illustrate how each value works.

ORDER=COLLATE

When you specify ORDER=COLLATE, SAS prints a list of variables in alphabetical order, beginning with uppercase and then lowercase names, as shown in the following output:

Alphabetic List of Variables and Attributes

#    Variable    Type    Len

1    Id          Num       8
3    Sales       Num       8
7    X10         Num       8
2    amount      Num       8
4    returns     Num       8
5    x1          Num       8
6    x2          Num       8

ORDER=COLLATE is not operating-system dependent, so you can compare the results across different systems.

ORDER=CASECOLLATE

When you specify ORDER=CASECOLLATE, SAS prints a list of variables in alphabetical order, ignoring the case of the letters, as shown in the following output:

Alphabetic List of Variables and Attributes

#    Variable    Type    Len

2    amount      Num       8
1    Id          Num       8
4    returns     Num       8   
3    Sales       Num       8
5    x1          Num       8
7    X10         Num       8
6    x2 

When variable names contain numbers and you sort with the CASECOLLATE value, variable names that contain numbers are also sorted alphabetically rather than in sequential numeric order. So, for example, in the output above, X10 comes before x2 (as opposed to listing x2 and then X10, as would be the case if the sort were in sequential numeric order).

ORDER=IGNORECASE

When you specify ORDER=IGNORECASE, SAS prints a list of variables in alphabetical order, without regarding the case of the letters, as illustrated here:

Alphabetic List of Variables and Attributes

#    Variable    Type    Len

2    amount      Num       8
1    Id          Num       8
4    returns     Num       8
3    Sales       Num       8
5    x1          Num       8
6    x2          Num       8
7    X10         Num       8

This value differs from CASECOLLATE in that it sorts variable names that contain numbers first alphabetically, and then by sequential numeric order. As shown in the output above, the order for the numbers is sequential: x1, x2, and then X10.

Conclusion

By using these new OPTION= values in conjunction with ODS output, you can sort your PROC CONTENTS output in several ways. For additional examples of how to use these new values, see the Base SAS® 9.2 Procedures Guide.

See the Sample Code section below for complete code that creates a data set and then illustrates how to sort that data set using the three methods described in this article: ODS, PROC SQL, and the new ORDER= values in PROC CONTENTS.

Sample Code

   /* Create a sample data set. */
data a;
   length x1 x2 X3 x4 X5 a1 a2 a3 A4 A5 8;
run;


   /* Use ODS to create a data set. */
ods listing close;
ods output Contents.DataSet.Variables=work.varlist;

proc contents data=work.a; 
run;

ods output close;
ods listing;

data work.varlist;
   set work.varlist;
   lowcase_variable= lowcase(variable);
run;


   /* Sorting Method 1: PROC SORT */
proc sort data=work.varlist       
          out=work.varlist(drop=lowcase_variable);
   by lowcase_variable;
run;

proc print data=work.varlist noobs;
   var num variable type len;
run;


   /* Sorting Method 2: PROC SQL dictionary table */
proc sql;
  select varnum, name, type, length from dictionary.columns
  where libname = 'WORK' and memname = 'A'
  order by lower(name);
quit;


   /* Sorting Method 3: PROC CONTENTS and the                
   ORDER= option */
ods listing select Contents.DataSet.Variables;
proc contents data=work.a order=ignorecase;
run;
ods listing all;