15

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

mj023119
  • 675
  • 5
  • 12
  • 19

5 Answers5

28

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;
Laurent de Walick
  • 2,154
  • 14
  • 11
21

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 ;
Chris J
  • 7,549
  • 2
  • 25
  • 25
  • 5
    +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. – Tom Quarendon Mar 21 '11 at 17:43
  • Hi Chris, I will try out your method later. thank you all the same:) – mj023119 Mar 24 '11 at 01:32
  • I know proc contents, but this is much more flexible. Thanks a lot! – Matt Jul 10 '15 at 09:50
  • I liked PROC CONTENTS because it preserved the original order of the variables. – Andrew Apr 13 '21 at 03:12
8
%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

Allan Bowe
  • 12,306
  • 19
  • 75
  • 124
purnendumaity
  • 81
  • 1
  • 1
  • 1
    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. – Allan Bowe Jan 14 '15 at 13:39
3

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.

moodymudskipper
  • 46,417
  • 11
  • 121
  • 167
Jan Brentved
  • 31
  • 1
0

I'm not sure Rawfocus assertion that reading dictionary tables queries all libraries is true, had the example used sashelp.vcolumn instead then it would be true, that approach is very slow and does access all the libraries allocated. (You can prove this with the SAS RTRACE system option.)

I am of the opinion that a sql query to dictionary.columns is the fastest of the methods outlined here. Obviously the macrotised code would work without the macro but the point of the macro here is I think as a utility; put the code into your favourite macro library and you never need to think about it again.

Smithers
  • 1