BookmarkSubscribeRSS Feed
Pyrite | Level 9

 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;


And also if possible same for Import to sas datasets?

Diamond | Level 26 RW9
Diamond | Level 26

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;
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;'));

ods tagsets.excelxp close;

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



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 3 in conversation