topic Re: Using output of proc contents to generate list of field names to store in a macro variable in SAS Programming

Using output of proc contents to generate list of field names to store in a macro variable

PegaZeus — Wed, 28 Oct 2020 15:16:27 GMT

Hello,

I'm attempting to create a list of fields stored in a macro variable to be used in a subsequent proc sql. I start the process with a proc contents on my input table to generate the field names into a table called "fields" like such:

proc contents data=egtask.my_data out=fields;
run;

What I'm getting stuck on is converting that to a list that you could put in a macro variable, and then use that in a proc sql;

%let field_list =  t1.system_id,  t1.system_name, t1.core_id, t1.core_measure

proc sql;
  create table my_new_data as
  select 
         &field_list,
         t1.id,
         t1.ind_code
from auth_data;
quit;

So like that except I want &field_list to be populated with the fields in "fields" automatically and not have to hard code the variables.

Re: Using output of proc contents to generate list of field names to store in a macro variable

Tom — Wed, 28 Oct 2020 15:19:15 GMT

Use the INTO clause of PROC SQL SELECT statement.

proc sql noprint;
  select name into :varlist separated by ',' from fields;
  create table my_new_data as
  select &varlist
       , id
       , ind_code
    from auth_data
  ;
quit;

Re: Using output of proc contents to generate list of field names to store in a macro variable

Reeza — Wed, 28 Oct 2020 15:39:22 GMT

proc sql noprint;
select name into :field_list separated by ", "
from sashelp.vcolumns
where libname = 'EGTASK' and memname = 'MY_DATA';
quit;

%put &field_list.;

%put Number of Entries : &sql_obs;

Re: Using output of proc contents to generate list of field names to store in a macro variable

FreelanceReinh — Wed, 28 Oct 2020 15:40:00 GMT

Hello @PegaZeus,

You can also pull the variable names from DICTIONARY.COLUMNS. Then you don't need a separate PROC step, nor a temporary dataset to store the names:

proc sql noprint;
select name into :field_list separated by ','
from dictionary.columns
where libname='EGTASK' & memname='MY_DATA';

create table my_new_data as
select &field_list,
       ...
quit;

If the variable names need to be prefixed, e.g., with "t1." for the CREATE TABLE statement, add the prefix in the first SELECT statement:

select 't1.'||name into ...