topic Re: How to export all data sets from a library to excel? in SAS Procedures

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

rajeshalwayswel — Fri, 24 Nov 2017 11:51:39 GMT

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?

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

RW9 — Fri, 24 Nov 2017 12:09:30 GMT

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.

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

Kurt_Bremser — Fri, 24 Nov 2017 12:15:54 GMT

Do a google search for "sas export multiple datasets to excel", and you'll find enough answers to keep you busy for days.