Export Variable names from numerous different library locations

[Sean_OConnor]

Folks,

Perhaps someone could provide some input into the following, please?

At the moment this is my sample code.

/*Sample code for extracting variable information into an excel sheet*/
/*1 - Read in dataset(s)*/
libname x1 "location1";
libname x2 "location2";
libname x2 "location3";
/*2 - Create dataset(s) with variable info*/
proc contents data=x1._all_
out=contentslisting1;
run;
proc contents data=x1._all_
out=contentslisting1;
run;
/*3- Export data to excel*/
PROC EXPORT DATA=contentslisting1 outfile="location\location\location" dbms=xlsx; 
sheet="2009";
run;
PROC EXPORT DATA=contentslisting2 outfile="location\location\location" dbms=xlsx; 
sheet="2010";
run;
PROC EXPORT DATA=contentslisting3 outfile="location\location\location" dbms=xlsx; 
sheet="2011";
run;

In some cases I might have to export data for 20 years so would have to do each step 20 times. Is there a way to quicken this process even more? 

[Reeza]

If you're on SAS 9.4 use SASHELP.VTABLE or VCOLUMN to pull the variables of interest + ODS Excel to split into a single Excel workbook. 

ODS EXCEL is only available in SAS 9.4+

Also, blog post from yesterday 🙂

http://blogs.sas.com/content/sastraining/2017/03/23/exploring-the-content-of-the-dictionaries-table-...

[Sean_OConnor]

Hi, unforunately I do not have SAS 9.4. Is it possible to provide a macro soloution? 

[ballardw]

Instead of macro please look at this:

proc sql;
   create table alldata as
   select *
   from dictionary.columns
   where libname in ('X1','X2','X3')
   order by libname, memname
   ;  
quit;


ods tagsets.excelxp file="path\file.xml"
 style=minimal  options(sheet_interval="Bygroup");

 proc print data=alldata noobs label;
   by libname;
 run;
ods tagsets.excelxp close;

This creates an XML file that Excel can read. If you truly need an XLSX file when done the do a file save as.

The sheet names will have the library name on the tab so a more descriptive name for the libraries than X1 and X2 would be a good idea.

Note that tagsets.excelxp will report on its own help documentation with this code:

ods tagsets.excelxp options(doc='Quick');

or

ods tagsets.excelxp options(doc='Help');

for the longer version.

[Sean_OConnor]

Thanks for this, the code is extremley useful. I'm just wondering however, is it limited to a set number of libnames? For instance I'm actually calling from 27 different libraries but it only process up to 9 and stops then?

[ballardw]

I don't think there should be any limits in normal practice. If you have enough data sets and variables in them that the total number of rows exceeds what a single sheet, or the number of sheets, Excel can display then you would have had that problem with your original approach.

If you have many libraries to select from it may be simpler in the code to use

Where LIBNAME not in ('SASHELP', 'SASUSER', 'WORK') (and any other libraries to exclude that may have currently defined that you do not want included)

Note that the value of LIBNAME in the dataset created will be all capital letters so you need to use all caps in your syntax.

[Reeza]

ODS Tagsets.ExcelXP is another option. 

A macro is another option for the export stage. 

CALL EXECUTE with proc export is a better idea. 

Here's an example but instead of creating a dataset change the code to export. 

https://gist.github.com/statgeek/4bfb7574713bedf4e011

[rogerjdeangelis]

Using meta data to create mutiple exce sheets in one workbook

inspired by
https://goo.gl/3ekrmk
https://communities.sas.com/t5/SAS-Enterprise-Guide/Export-Variable-names-from-numerous-different-library-locations/m-p/344074

HAVE Meta data and three datasets (Inputs and outputs)
======================================================

Up to 40 obs WORK.META total obs=3

Obs    PTHINP     SHEET    TABLE

 1     d:/sd1/    2009      x1
 2     d:/sd2/    2010      x2
 3     d:/sd3/    2011      x3

Three SAS datasets

    d:/sd1/X1.sas7bdat
    d:/sd2/X2.sas.bdat
    d:/sd3/X3.sas7bdat

X1  Up to 40 obs from sd1.x1 total obs=3

Obs     NAME      SEX    HEIGHT    WEIGHT

 1     Alfred      M      69.0      112.5
 2     Alice       F      56.5       84.0
 3     Barbara     F      65.3       98.0

X2  Up to 40 obs from sd2.x2 total obs=3

Obs     NAME      SEX    AGE    HEIGHT

 1     Alfred      M      14     69.0
 2     Alice       F      13     56.5
 3     Barbara     F      13     65.3

X3  Up to 40 obs from sd3.x3 total obs=3

Obs     NAME      SEX    AGE    WEIGHT

 1     Alfred      M      14     112.5
 2     Alice       F      13      84.0
 3     Barbara     F      13      98.0



WANT (three worksheets 2009, 2010, 2011 in workbook d:/xls/sheets.xlsx)
=======================================================================

Workbook d:/xls/sheets.xlsx

2009 ( Variable names from dataset d/sd1/X1.sas7bdat)

  +------------+
  |     A      |
  -------------+
1 |  VARIABLE  |
  +------------+
2 |  NAME      |
3 |  SEX       |
4 |  HEIGHT    |
5 |  WEIGHT    |
  +------------+

[2009]


2010 ( Variable names from dataset d/sd1/X2.sas7bdat)

  +------------+
  |     A      |
  -------------+
1 |  VARIABLE  |
  +------------+
2 |  NAME      |
3 |  SEX       |
4 |  AGE       |
5 |  HEIGHT    |
  +------------+

[2010]


2011 ( Variable names from dataset d/sd1/X3.sas7bdat)

  +------------+
  |     A      |
  -------------+
1 |  VARIABLE  |
  +------------+
2 |  NAME      |
3 |  SEX       |
4 |  AGE       |
5 |  WEIGHT    |
  +------------+

[2011]

WORKING CODE
===========

       1 DOSUBL (load meta data)
       2 Datastep code to operate on meta data
       3 DOSUBL (create excel sheets

*                _                  _       _
 _ __ ___   __ _| | _____        __| | __ _| |_ __ _
| '_ ` _ \ / _` | |/ / _ \_____ / _` |/ _` | __/ _` |
| | | | | | (_| |   <  __/_____| (_| | (_| | || (_| |
|_| |_| |_|\__,_|_|\_\___|      \__,_|\__,_|\__\__,_|

;

libname sd1 "d:/sd1";
libname sd2 "d:/sd2";
libname sd3 "d:/sd3";

data sd1.x1(drop=age)
     sd2.x2(drop=weight)
     sd3.x3(drop=height);
  set sashelp.class(obs=3);
run;quit;

data meta;
 informat pthinp sheet $7. table $2.;
 input pthinp sheet table;
cards4;
d:/sd1/ 2009 x1
d:/sd2/ 2010 x2
d:/sd3/ 2011 x3
;;;;
run;quit;

*          _       _   _
 ___  ___ | |_   _| |_(_) ___  _ __
/ __|/ _ \| | | | | __| |/ _ \| '_ \
\__ \ (_) | | |_| | |_| | (_) | | | |
|___/\___/|_|\__,_|\__|_|\___/|_| |_|

;

%utlfkil(d:/xls/sheets.xlsx);
libname xel "d:/xls/sheets.xlsx";

data _null_;
  if _n_=0 then do;
    %let rc=%sysfunc(dosubl('
      proc sql;
        select
            quote(trim(pthinp))
           ,quote(trim(sheet))
           ,quote(trim(table))
        into
           :inp separated by ","
          ,:she separated by ","
          ,:tbl separated by ","
        from
           meta
      ;quit;
    '));
  end;

  array inps[&sqlobs] $7  (&inp);
  array shes[&sqlobs] $4  (&she);
  array tbls[&sqlobs] $32 (&tbl);

  do i=1 to &sqlobs;

    call symputx('inpx',inps[i]);
    call symputx('shex',shes[i]);
    call symputx('tblx',tbls[i]);

    rc=dosubl('
      libname inp "&inpx.";
      proc transpose data=inp.&tblx.(obs=1)
          out=xel.f&shex.(rename=_name_=variable);
        var _all_;
      run;quit;
    ');

  end;
run;quit;

libname xel clear;