How to read variable names in a SAS data set?

Question

Are there any statements\functions capable of get the name of variables? Preferrably putting them into a column of another data set, a text field or a macro variable.

E.g.

- Data set 1

Name age sex

    Jk   14   F
    FH   34   M

• Expected data set

  Var_name_of_dataset1

  Name
  age
  sex
  

PS: I know a statement: select into, which does sth relevantly It can read the value of a column into a field with customized separetors, and therefore wish there are similar ways of reading column names into a field or a column.

Thanks

Solution 1

PROC CONTENTS would be the quickest way to get that information in a dataset. Column names can be found in the column NAME.

proc contents data=sashelp.class out=contents noprint;
run;

Solution 2

You can also use a datastep and array functions, e.g.

data colnames ;
  set sashelp.class (obs=1) ;

  array n{*} _NUMERIC_ ;
  array c{*} _CHARACTER_ ;

  do i = 1 to dim(n) ;
    vname = vname(n{i}) ;
    output ;
  end ;
  do i = 1 to dim(c) ;
    vname = vname(c{i}) ;
    output ;
  end ;
run ;

Solution 3

%macro getvars(dsn);
 %global vlist;
 proc sql;
 select name into :vlist separated by ' '
  from dictionary.columns
  where memname=upcase("&dsn");
 quit;
%mend;

This creates a macro variable called &vlist that will contain the names of all the variables in your dataset, separated by a space. If you want commas between the variable names, all you have to do is change the 'separated by' value from ' ' to ', '. The use of the upcase function in the where statement avoids problems with someone passing the dataset name in the wrong case. The global statement is needed since the macro variable created will not necessarily be available outside the macro without defining it as global

Solution 4

Slightly changed from SAS help and documentation.

%macro names(dsid);
  %let dsid=%sysfunc(open(&dsid, i));
  %let num=%sysfunc(attrn(&dsid,nvars));
  %let varlist=;
  %do i=1 %to &num  ;
    %let varlist=&varlist %sysfunc(varname(&dsid, &i));
  %end;
  %let rc = %sysfunc(close(&dsid)); /*edit by Moody_Mudskipper: omitting this line will lock the dataset */
  %put varlist=&varlist;
%mend names;

%names(sasuser.class) ;

Then we preserve case and the order off data, even if numeric and character is mixed.

View more solutions

Comments

[Tom Quarendon]

+1: I think this is probably the most useful and flexible method. You can output what you want from the data step -- a data set of a specific format, or CALL SYMPUT to set macro variables in a given style. You can also do VLABEL, VFORMAT etc to get other information.

[mj023119]

Thanks a lot Laurent!This is very useful. CHeers

[mj023119]

Hi Chris, I will try out your method later. thank you all the same:)

[Allan Bowe]

a few issues - firstly, the dictionary table here can be very slow, as it queries all libraries. Secondly, that upcase function will fire on every record.. %upcase would be more efficient. Finally, why even use a macro? This would work fine in open code.

[Matt]

I know proc contents, but this is much more flexible. Thanks a lot!

[zuluk]

Do not forget to close the dataset: %sysfunc(close(&dsid));

[moodymudskipper]

@zuluk where to put this line ? I can't make it work

[zuluk]

Write these lines at the end of the macro: %let rc = %sysfunc(close(&dsid)); %put varlist=&varlist; %mend names; I also do not have sasuser.class. For me sashelp.class works.

[Andrew]

I liked PROC CONTENTS because it preserved the original order of the variables.