Listing Column Names in SAS - SASlearner

Sometimes we would want to list column names of a SAS dataset, which can be useful in other programs, such as proc freq, proc transpose.

In other programming languages like R, it’s very easy and convenient to show column names as following:

> names(iris)
[1] "Sepal.Length" "Sepal.Width"  "Petal.Length" "Petal.Width"  "Species"     
> colnames(iris)
[1] "Sepal.Length" "Sepal.Width"  "Petal.Length" "Petal.Width"  "Species"

In SAS, as I know, maybe there are two approaches to accomplish the same purpose.

One way is to use proc contents, which seems very straightforward.

proc contents data = sashelp.class;
run;

If we want to store them in a new table (dataset), it can be used like this.

proc contents data = sashelp.class memtype = data
    out = cols nodetails noprint;
run;

The second way to list the column names is with the use of a direction table. That way is also very common for other purposes.

I learned from Google that we can use either proc data or proc sql procedure.

For proc data step, sashelp.vcolumn is a view on the direction table, so it's just like to filter the rows of sashelp.vcolumn dataset.

data columns;
    set sashelp.vcolumn;
    where libname = 'sashelp' and memname = 'class';
run;

If we would like to save the column names as a macro variable, then I feel proc sql is a better solution. So it corresponds to the dictionary.columns table.

proc sql;
    select name into: cols seperated by ' '
        from dictionary.columns
            where libname = 'SASHELP' and memname = 'CLASS';
quit;

%put &cols;

If you're not sure which column variable is what you want, try the describe below.

proc sql;
    describe table dictionary.columns;
quit;

/* output, not run */
create table DICTIONARY.COLUMNS
  (
   libname char(8) label='Library Name',
   memname char(32) label='Member Name',
   memtype char(8) label='Member Type',
   name char(32) label='Column Name',
   type char(4) label='Column Type',
   length num label='Column Length',
   npos num label='Column Position',
   varnum num label='Column Number in Table',
   label char(256) label='Column Label',
   format char(49) label='Column Format',
   informat char(49) label='Column Informat',
   idxusage char(9) label='Column Index Type',
   sortedby num label='Order in Key Sequence',
   xtype char(12) label='Extended Type',
   notnull char(3) label='Not NULL?',
   precision num label='Precision',
   scale num label='Scale',
   transcode char(3) label='Transcoded?',
   diagnostic char(256) label='Diagnostic Message from File Open Attempt'
  );

By now we get the macro variable called cols, then it can be used in any procedure like proc freq.

proc freq data = sashelp.class nlevels;
    tables &cols;
run;

Do you think which one is the better solution? It’s no doubt that it depends on your actual case.

---

Reference

Code to list table columns and data types
How to List Column Names in SAS

Please indicate the source: http://www.bioinfo-scrounger.com