I have the following code with me. It outputs 3 tables in the "results" section for each data set. Third table in the "results" section has the names of the variables along with other details such as type, length, etc. I need to combine this third table for each of these data sets so that finally, I just have one table which has the names (and type, length, etc.) of all the variables that are there in all of these tables.
Basically, my goal is to merge all the third tables in the "Results" section so as to get all this information in just one table.
Help would be appreciated. Thank you.
libname a meta library="abc" metaout=data; data list; infile datalines4 dlm='|'; format table $32.; input table; datalines4; table_a table_b table_c table_d table_e table_f table_g table_h ;;;; run; data _null_; set list; call execute(' proc contents data=a.'||strip(table)||'; run; '); run;
Sure. See if you can use this as a template. Below, I create 3 data sets for demonstration, table_a, table_b and table_c. I simplify your list data a bit to have those 3 data sets listed. Then I use the dictionary.columns metadata to retrieve information from the tables listed in the list data set.
Obviously, you would have to change the WORK library to A below.
Let me know if it works for you 🙂
data table_a; set sashelp.class; run;
data table_b; set sashelp.class; run;
data table_c; set sashelp.class; run;
data list;
infile datalines4 dlm='|';
format table $32.;
input table;
datalines4;
table_a
table_b
table_c
;;;;
run;
proc sql;
create table want as
select a.*
from dictionary.columns a
, list b
where upcase(a.memname) = upcase(b.table)
and libname = 'WORK'
;
quit;
All this information is already available in dictionary.columns and sashelp.cvcolumn metadata tables. No need to jump hoops like this.
data want;
set sashelp.vcolumn;
where libname = 'SASHELP';
run;
Sure. See if you can use this as a template. Below, I create 3 data sets for demonstration, table_a, table_b and table_c. I simplify your list data a bit to have those 3 data sets listed. Then I use the dictionary.columns metadata to retrieve information from the tables listed in the list data set.
Obviously, you would have to change the WORK library to A below.
Let me know if it works for you 🙂
data table_a; set sashelp.class; run;
data table_b; set sashelp.class; run;
data table_c; set sashelp.class; run;
data list;
infile datalines4 dlm='|';
format table $32.;
input table;
datalines4;
table_a
table_b
table_c
;;;;
run;
proc sql;
create table want as
select a.*
from dictionary.columns a
, list b
where upcase(a.memname) = upcase(b.table)
and libname = 'WORK'
;
quit;
@PeterClemmensen , unfortunately it doesn't work for me. Attached with this comment is the picture of what my screen shows me. I appreciate your effort and have upvoted your comments but I was wondering if you know another way to do this, preferably following the line of thought in this:
data _null_;
set list;
call execute('
proc contents data=a.'||strip(table)||';
run;
');
run;
Maybe, the code could merge/append as it loops?
What does not work? 🙂 Do you get an error or simply different results than what you expect? Please be specific.
Please post the log from the code you ran.
The 'a' should be a capital 'A' in the code.
If all you care about is to get a list of variables present in at least one of the datasets, then just make an empty dataset of all the component datasets, and run a proc contents:
data dummy;
set a.table_a
a.table_b
a.table_c
a.table_d
a.table_e
a.table_f
a.table_g
a.table_h ;
stop;
run;
proc contents data=dummy;
run;
Note the "stop:" statement tells the data step to stop, even before the first observation is processed. So no excess input/output is performed. But it will write out the header. So you'll have zero obs, but all the variables.
Note this assumes that any common variable will be either numeric in every instance, or character in every instance.
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.