How to List Column Names in SAS

Listing Column Names in SAS

In this article, we explain how to list the column names of a SAS data set. We store them in a new table and save them as a macro variable. Listing column names can be useful to create more dynamic SAS programs. We demonstrate this with an example.

Suppose you have a table that you want to transpose. However, the structure of the table might change over time. For example, new columns might be added and others might be removed. We show how to create a SAS program that can transpose your table even if its structure changes.

First, we discuss how to list all the column names and the character or numeric columns only. Then we show how to create a macro variable with the column names. Finally, we use this macro variable in a PROC TRANSPOSE statement.

Two Ways to List Column Names

The first option to list the column names of a SAS data set is with the PROC CONTENTS statement. With the syntax below you store the column names in a new table. If you don’t specify the keep options, then the output table will contain not only the column names but also its format, its length, its type, etc.

proc contents data= (input-data-set) out=(output-data-set (keep=NAME));
run;

The second option to list the column names is with the use of a dictionary table. The code below show how to do it.

proc sql;
create table output-data-set as
select NAME
from dictionary.columns
where libname = ‘libref‘
and memname = ‘input-data-set‘;
quit;

If you only want the character or numeric columns, you can add another WHERE condition.

proc sql;
create table output-data-set as
select NAME
from dictionary.columns
where libname = ‘libref‘
and memname = ‘input-data-set‘
and type = ‘char’;
quit;

Example: Listing Column Names & Transposing a Table

The Data Set

For this example, we use information about the IBM stock price in 2005. The first image below shows the table we have and the second shows the transposed table we want.

The original table is a subset of the STOCKS data set in the SASHELP library.

data work.ibm_2005_stock;
	set sashelp.stocks (where=(stock='IBM' and year(date) = 2005));
run;

Listing the Column Names & Creating a Macro Variable

In the transposed table, the dates will be the new column names and all other columns will be rows. Since new columns might be added or other columns might be removed, we need to list all columns, except the date column and store them in a macro variable. The code below shows how to do this with the PROC INTO statement.

/* Create a List of All Column Names, except 'DATE' */
proc sql noprint;
	select name into: col_names_transpose separated by ' '
		from dictionary.columns
		where libname = 'WORK'
		and memname = 'IBM_2005_STOCK'
		and name ne 'Date';
quit;

%put &=col_names_transpose.;

Transpose the Data Set

With the use of the previously created macro variable col_names_tranpose, we can easily transpose the original data set. Since we want the new columns (the date) to be in ascending order, we need to order the data set first. The code below shows how to do this.

/* Sort input table */
proc sort data=work.ibm_2005_stock;
	by Date;
run;

/* Transpose Table */
proc transpose data=work.ibm_2005_stock
	name = Col_Name
	prefix = Date_
	out = work.ibm_2005_stock_trans (drop=_LABEL_);
	
	id Date;
	var &col_names_transpose.;
run;