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
PROC Star

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.

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.
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.

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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