topic How to create a macro variable with all variable in a dataset in SAS Programming

How to create a macro variable with all variable in a dataset

knveraraju91 — Fri, 15 May 2020 20:27:10 GMT

Dear

I need to create a macro with all name in a data set. Please suggest

data one;
input a b c;
datalines;
1 2 3
3 4 4
;
proc sql;
select name into: var separated by ""
from one;
quit;

output needed:

var=a b c

Thank you

Re: How to create a macro variable with all variable in a dataset

Kurt_Bremser — Fri, 15 May 2020 20:32:59 GMT

Use dictionary.columns as source, where libname = 'WORK' and memname = 'ONE'.

Re: How to create a macro variable with all variable in a dataset

yabwon — Sat, 16 May 2020 08:18:18 GMT

Hi,

two other approaches (avoiding dictionary.columns):

/* one way: */
data _null_; 
  length n $ 32000; 
  do di=open('sashelp.class', 'I') while(di ne 0); 
    do i=1 to attrn(di,'NVARS'); 
      n=catx(" ", n, varname(di,i)); 
    end; 
  end; 
  call symputx('var', n);
run; 

%put &=var;


/* second way */
proc transpose data = sashelp.cars(obs=0) out = _tmp(keep = _name_);
  var _all_;
run;
proc sql noprint;
  select _name_ 
  into :var separated by " "
  from _tmp;
  drop table _tmp;
quit;

%put &=var;

All the best

Bart

Re: How to create a macro variable with all variable in a dataset

PaigeMiller — Sat, 16 May 2020 10:50:40 GMT

Yet another approach is to use PROC CONTENTS to determine the column names in your data set, and then PROC SQL to create the macro variable. I find this preferable to using dictionary tables, as the dictionary tables can be very slow if you have databases with lots of tables/variables opened, or if you have many LIBNAMEs active (or both).