Tag Archives: excel

Execute Excel Commands from SAS

There are a few ways to execute Excel commands from SAS, but this method uses DDE (“Dynamic Data Exchange”) to facilitate communication between the two applications. For this example, I want to open 25 multisheet XML workbooks in Excel, call an Excel macro to run for each tab, and then re-save the XML files as XLSX files before closing them. In order to cycle through each of these XML output files, I also use a macro in SAS, which I run using call execute in a data step. Continue reading →

Create a Data Dictionary Part II: SQL Dictionary Tables

I have already posted on how to export a data dictionary to Excel using a modified Proc Contents. However, you can also practice using the Excel libname statement with SQL dictionary tables to do the same thing. The advantage of using SQL dictionary tables over Proc Contents is that SQL dictionary tables store much more information and give you access to many more variables. The two tables I use in the following code are dictionary.tables and dictionary.columns.

First, you can use Proc SQL with the noexec and feedback options to get a full list of available variables for each table to print to the log without actually having to execute the sql statement (for more on the feedback option, see my previous post):

89 proc sql feedback noexec;
90 select *
91 from dictionary.tables;

NOTE: Statement transforms to:
select libname, memname, memtype, dbms_memtype, memlabel, typemem, crdate, modate, nobs, obslen, nvar, protect, compress, encrypt, npage, filesize,pcompress, reuse, bufsize, delobs, nlobs, maxvar, maxlabel, maxgen, gen, attr, indxtype, datarep, sortname, sorttype, sortchar, reqvector, datarepname,encoding, audit, audit_before, audit_admin, audit_error, audit_data
from DICTIONARY.TABLES;

Now, suppose I want to create my data dictionary based on all the tables I have in my library “HRA”. First, I assign my libname statement to point to the Excel workbook I want to create (note that the libname statement does not support the replace option, so if the file already exists, you’ll have to delete it before trying to recreate it):

libname myxls \\WLDAPP043\Hedisapp\Personal\Jess\HRA\2010\Data_Dictionary.xls;

Then I select from the dictionary tables using Proc SQL to create my two worksheets:

proc sql;
create table myxls.directory as
select memname,
crdate,
modate,
nobs,
obslen,
nvar,
filesize
from dictionary.tables
where libname = ‘HRA’
;
create table myxls.table_listing as
select memname,
name,
type,
length,
format
from dictionary.columns
where libname = ‘HRA’
;
QUIT;

One final note to add is that you can’t open the Excel file while the libname is still assigned, so you’ll either have to close out of SAS before admiring your data dictionary, or run the following line of code to deassign your libname statement.

libname myxls clear;

Create an Excel Data Dictionary with Proc Contents and Proc Export

Sometimes it may be useful to generate a table listing of all the SAS datasets within a folder (the code below uses the previously assigned libref HRA to refer to this folder) — a sort of table of contents — along with some more detail about the fields within those tables. While this information can be output within SAS using a Proc Contents, you may wish to organize this information differently, keep and drop certain variables, and export it in the form of a multi-sheet Excel workbook. The code below shows how a simple data dictionary can be created using Proc Contents and exported to Excel with Proc Export (or alternatively, the Libname statement). Both of these options for exporting data to Excel were discussed in a previous post. Keep in mind that although the code below only selects a very few of the variables available in SAS, you can modify it to select other variables as well. Try it without the keep= option to see the full range of available variables.

Data Dictionary with Proc Export:

PROC CONTENTS DATA=HRA._all_ memtype=data Out=table_listing (keep=memname Nobs name length format crdate);
RUN;

PROC SQL;
Create table directory as
SELECT distinct memname, nobs, crdate
from table_listing;
QUIT;

PROC EXPORT DATA=directory
OUTFILE=”\\path\Data_Dictionary.xls”
DBMS=EXCEL REPLACE;
SHEET=”directory”;
RUN;

PROC EXPORT DATA=table_listing
OUTFILE=”\\path\Data_Dictionary.xls”
DBMS=EXCEL REPLACE;
SHEET=”table_listing”;
RUN;

Alternate Syntax with Libname Statement:

libname myxls “\\path\Data_Dictionary.xls”; /*does not support replace option*/

PROC CONTENTS DATA=HRA._all_ memtype=data Out=myxls.table_listing (keep=memname Nobs name length format crdate);
RUN;

PROC SQL;
Create table myxls.directory as
SELECT distinct memname, nobs, crdate
from myxls.table_listing;
QUIT;

3 Ways to Export Your Data to Excel

SAS offers many different ways to export data to Excel, including Excel ODS output. The following alternatives to ODS are also available:

The File Export Wizard

This is an easy way to export your data without having to write any code. To use this wizard:

  • Select File>>Export Data
  • Choose the library and SAS data set name
  • Select a data source from the dropdown list (in this case, the version of Excel you’d like to export to – other types of data sources are also available here)
  • Browse to the folder where you’d like to create your Excel Workbook and type a name for the file
  • You then have the option to save the Proc Export statements generated by the Wizard to a SAS program file

Proc Export

If you chose to use the File Export Wizard to save your Proc Export statements, your code may look something like this:

PROC EXPORT DATA=sasdatasetname
OUTFILE=”\\path\filename.xls”
DBMS=EXCEL REPLACE;
SHEET=”sheetname”;
RUN;

  • The data set you’d like to export goes after DATA=
  • The file path goes after OUTFILE=
  • You are exporting to Excel, so DBMS=EXCEL
  • If you wish to replace any existing workbook or worksheet with the same name, use the REPLACE option.
  • The SHEET= statement is optional, but if you wish to create a multi-sheet workbook, it is useful. For your next PROC EXPORT statement, just specify a different file and a new SHEET name.

The Libname Statement

This is a newer option for exporting to Excel which does not support the REPLACE option, so if you already have a file of the same name you are trying to create, you will have to delete or rename the older version first. Also, while the libname is assigned, you will not be able to access the Excel document you have created; you will first have to exit out of SAS or clear the libname. It is easy to create multiple sheets using this method, though, as shown below.

libname myxls \\path\filename.xls;

DATA myxls.sheetname1;
SET sasdataset1;
DATA myxls.sheetname2;
SET sasdataset2;
RUN;

PROC SQL;
Create table myxls.sheetname3 as
SELECT *
from sasdataset3;
QUIT;