exporting in SAS multiple datasets

Question

I have a set of datasets which I would like to output in an Excel File. Is there a way to do this quickly rather than calling proc export each time for each dataset

%let MyDS = ('out.Ids', 'out.Vars', 'out.Places')

%let MyDSname = (Ids, Vars, Places)

I would like to create a macro that check if each dataset exists and then output to an Excel spreadsheet with the Tab name as specified in the corresponding MyDSname ...

Something like... %macro Out(MySpreadsheetName, MyDS, MyDSname);

Thanks very much for your help

Joe Joe · Accepted Answer · 2014-02-25T17:18:31

Assuming you have Access to PC Files licensed, the easiest way to do this (export several datasets to one workbook) is with a libname.

libname mywbk excel 'c:\pathtomyexcel\excelfile.xlsx';

data mywbk.nameoftab;
set dataset;
run;

In terms of conditionally creating this, you should look at how you're arriving at the list of names to export. In general, you should have a dataset that contains one row per dataset to export, and two columns - DS name and tab name. You can then merge that to sashelp.vtables or dictionary.tables which are views containing the list of tables in the current SAS session; memname is the name of the table, libname is the name of the library. Then create a macro call from that:

proc sql;
select cats('%out(',dsname,',',tabname,')') into :calllist separated by ' '
from joinedds;
quit;

libname ... ;
&calllist.;

exporting in SAS multiple datasets

1 Answers