topic Re: sysfunc get all variable names from dataset in SAS Programming

sysfunc get all variable names from dataset

TimurShangareev — Thu, 17 Aug 2023 08:59:41 GMT

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.

Re: sysfunc get all variable names from dataset

PaigeMiller — Thu, 17 Aug 2023 10:07:39 GMT

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.

Re: sysfunc get all variable names from dataset

Ksharp — Thu, 17 Aug 2023 11:28:37 GMT

%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)

Re: sysfunc get all variable names from dataset

Tom — Thu, 17 Aug 2023 13:11:39 GMT

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|

Re: sysfunc get all variable names from dataset

Quentin — Thu, 17 Aug 2023 15:36:37 GMT

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.

Re: sysfunc get all variable names from dataset

Reeza — Thu, 17 Aug 2023 15:49:11 GMT

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.