How to export all data sets from a library to excel?

rajeshalwayswel · Posted 11-24-2017 06:51 AM

How to export all data sets from a library to excel? Example I'm containing "xyz" library contains 20 data sets I want it into excel in sheets? I'm aware about only one data set to export it into excel can anyone idea about it?

This I'm aware about single data set to excel.

proc export data=work outfile='C:\Users\Nagaraju\Desktop\f.xlsx' dbms=xlsx;
run;

And also if possible same for Import to sas datasets?

RW9 · Posted 11-24-2017 07:09 AM

Its a good idea to use the search for questions before posting, this question has been answered many times before. Question 1 what version of SAS, if you are using 9.4 then it is very simple:

libname tmp excel "<path to file>/<filename>.xlsx";
proc copy in=tmp out=work;
run;
libname tmp clear;

And vice versa for writing out, swap in to work, and out to tmp. (Assumes data is in the work library, change work to your library).

If your not on 9.4, then it becomes harder. Writing out you can do tagsets.excelxp, and a call execute, or a proc export, and call execute for each dataset.

ods tagsets.excelxp file="<pathtofile>/<filename>.xml";

data _null_;
  set sashelp.vtable (where=(libname="WORK"));
  call execute(cat('ods tasgets.excelxp options(sheet_name="',strip(memname),'");'));
  call execute(cat('proc report data=work.',strip(memname),' nowd; columns _all_; run;'));
run;

ods tagsets.excelxp close;

Reading in, there is no real way of doing automatically in < 9.4, you have to bodge it.