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