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.
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 ...
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;
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;
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 ...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.