topic Re: How to merge these tables into one? in SAS Programming

How to merge these tables into one?

SASuser4321 — Thu, 17 Nov 2022 10:24:56 GMT

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;

Re: How to merge these tables into one?

PeterClemmensen — Thu, 17 Nov 2022 10:35:56 GMT

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;

Re: How to merge these tables into one?

SASuser4321 — Thu, 17 Nov 2022 12:53:50 GMT

I tried something similar but it didn't work for me. What would your code in my case look like exactly? My libname is 'a' and the name of my set is 'list' as is shown in my original post above.

Re: How to merge these tables into one?

PeterClemmensen — Thu, 17 Nov 2022 13:07:07 GMT

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;

Re: How to merge these tables into one?

SASuser4321 — Thu, 17 Nov 2022 14:23:00 GMT

@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?

Re: How to merge these tables into one?

PeterClemmensen — Thu, 17 Nov 2022 14:20:46 GMT

What does not work? 🙂 Do you get an error or simply different results than what you expect? Please be specific.

Re: How to merge these tables into one?

SASuser4321 — Thu, 17 Nov 2022 14:23:55 GMT

I edited my above comment and added a picture of what the screen shows me as a result of the code 🙂

Re: How to merge these tables into one?

PeterClemmensen — Thu, 17 Nov 2022 14:27:16 GMT

Please post the log from the code you ran.

Re: How to merge these tables into one?

SASuser4321 — Thu, 17 Nov 2022 14:34:44 GMT

libname a meta library="xyz" metaout=data;
775 data list;
776 infile datalines4 dlm='|';
777 format table $32.;
778 input table;
779 datalines4;

NOTE: The data set WORK.LIST has 28 observations and 1 variables.
NOTE: Compressing data set WORK.LIST increased size by 100.00 percent.
Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

808 ;;;;
809 run;
810
811 proc sql;
812 create table want as
813 select a.*
814 from dictionary.columns a
815 , list b
816 where upcase(a.memname) = upcase(b.table)
817 and libname = 'a'
818 ;
NOTE: Table WORK.WANT created, with 0 rows and 18 columns.

819 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

820
821 GOPTIONS NOACCESSIBLE;
822 %LET _CLIENTTASKLABEL=;
823 %LET _CLIENTPROCESSFLOWNAME=;
824 %LET _CLIENTPROJECTPATH=;
825 %LET _CLIENTPROJECTPATHHOST=;
826 %LET _CLIENTPROJECTNAME=;
827 %LET _SASPROGRAMFILE=;
828 %LET _SASPROGRAMFILEHOST=;
829
830 ;*';*";*/;quit;run;
831 ODS _ALL_ CLOSE;
3 The SAS System Thursday, November 17, 2022

832
833
834 QUIT; RUN;
835 %_eg_gridremoteepilogue;
836 %sysrput _EGRCGRID=&_EGRC;
NOTE: Remote submit to GRID complete.
2 %_eg_gridclientepilogue;
3 %let _EGRC=&_EGRCGRID;
4

Re: How to merge these tables into one?

PeterClemmensen — Thu, 17 Nov 2022 14:43:45 GMT

The 'a' should be a capital 'A' in the code.

Re: How to merge these tables into one?

mkeintz — Thu, 17 Nov 2022 14:55:36 GMT

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.