sysfunc get all variable names from dataset

[TimurShangareev]

Is it possible to get all variable names from dataset by using %sysfunc() (i mean by using only macro code without datasteps and procedures like SQL")? So i could insert this call as argument for other user's defined macro function.

Probably it has to use %sysfunc(varname) function.

[PaigeMiller]

Why do you specify that it has to use %SYSFUNC()? It is counter-productive to place restrictions like this on what solution is allowed. And no data steps or SQL is allowed either? That's like saying to a carpenter, build me a cabinet but you're not allowed to use any kind of saw or drill.

So i could insert this call as argument for other user's defined macro function.

Again, its hard for me to imagine why this is relevant, arguments can be derived in any way that works.

Here's code that creates a macro variable named VARNAMES that contains the names of variables in data set SASHELP.CLASS:

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

&varnames can be an argument to someone else's macro.

[Ksharp]

%macro get_all_varname(dsn=);
%let dsid=%sysfunc(open(&dsn.));
%let nvar=%sysfunc(attrn(&dsid.,nvars));
%do i=1 %to &nvar.;
  %let vname=%sysfunc(varname(&dsid.,&i.));
  %put &=vname.;
%end;
%let dsid=%sysfunc(close(&dsid.));
%mend;

%get_all_varname(dsn=sashelp.heart)

[Tom]

Someone has a utility macro that does this that also allow filtering that they have posted multiple times.

But in general just open the dataset and loop over the set of variables.

%macro listvar(ds);
%local dsid i;
%let dsid=%sysfunc(open(&ds));
%if &dsid %then %do;
  %do i=1 %to %sysfunc(attrn(&dsid,nvars));
 %qsysfunc(varname(&dsid,&i))
  %end;
  %let i=%sysfunc(close(&dsid));
%end;
%mend listvar;

137  %put |%listvar(sashelp.class)|;
|Name  Sex  Age  Height  Weight|

[Quentin]

Another option for a function-style macro that returns a list of variables is to use DOSUBL.  This allows you to run PROC or DATA steps in the "side session", rather than rely on pure macro code.

%macro varlist(data) ;
  %local rc varnames ;
  %let rc = %sysfunc(dosubl(%nrstr(
    proc contents data=&data out=__VarList(keep=name) noprint ;
    run ;
    proc sql noprint ;
      select name into :varnames separated by ' '
      from __VarList ;   
      drop table __VarList ;
    quit ;
  )));

&varnames /*return*/
%mend ;

The nice thing is that you can then use the usual KEEP or DROP option to subset variables, e.g.:

%put %varlist(sashelp.class) ;
%put %varlist(sashelp.class(keep=_numeric_)) ;
%put %varlist(sashelp.class(drop=Age--Weight)) ;

To read more about DOSUBL, you'll want to start with Rick Langston's masterful introduction:

https://support.sas.com/resources/papers/proceedings13/032-2013.pdf

DOSUBL will run more slowly than the %SYSFUNC(open()) approach, because DOSUBL has to do a good bit of overhead work in order to create the side-session.

[Reeza]

Are you intending to use this within the data step itself on the same dataset?

If so, may be worth exploring call vnext a bit.