BookmarkSubscribeRSS Feed
TimurShangareev
Calcite | Level 5

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.

5 REPLIES 5
PaigeMiller
Diamond | Level 26

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.

 

 

--
Paige Miller
Ksharp
Super User
%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
Super User Tom
Super User

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
Super User

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.

The Boston Area SAS Users Group (BASUG) is hosting an in person Meeting & Training on June 27!
Full details and registration info at https://www.basug.org/events.
Reeza
Super User
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.

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1744 views
  • 0 likes
  • 6 in conversation