Help using Base SAS procedures

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

Reply
Frequent Contributor
Posts: 91

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

 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?

Super User
Super User
Posts: 9,599

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

Posted in reply to rajeshalwayswel

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.

Super User
Posts: 10,271

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

Posted in reply to rajeshalwayswel

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Ask a Question
Discussion stats
  • 2 replies
  • 548 views
  • 0 likes
  • 3 in conversation