save datasets to excel

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 130
Accepted Solution

save datasets to excel

I have quite some data sets in work library, and would like to save those data sets whose file names begin with "tt_" to excel files (better if can put into one excel file with many spreadsheets).

Find those data sets one by one and save as excel file using the following statements is kind of time consuming.

ods excel file="C:\data\tt_xxxxxx.xlsx";

proc print data=tt_xxxxxx;

run;

ods excel close;

or

proc export dbms = excel2007

     outfile = "C:\data\tt_xxxxxx.xlsx"

     data=tt_xxxxxx replace;

     sheet = "tt_xxxxxx";

run;

thank you as always.

p.s. the xxxxxx part of the dataset name is random, does not following any pattern.


Accepted Solutions
Solution
‎08-12-2015 11:41 PM
Super User
Posts: 1,117

Re: save datasets to excel

Please try

proc sql;

select distinct memname into :name separated by '/' from dictionary.tables where libname='WORK' and memname like 'TT_%';

quit;

%macro test;

%do i = 1 %to &sqlobs;

%let dd=%scan(&name,&i,/);

ods excel file="C:\data\&dd..xlsx";

proc print data=ⅆ

run;

%end;

ods excel close;

%test;

Thanks,

Jag

Thanks,
Jag

View solution in original post


All Replies
Solution
‎08-12-2015 11:41 PM
Super User
Posts: 1,117

Re: save datasets to excel

Please try

proc sql;

select distinct memname into :name separated by '/' from dictionary.tables where libname='WORK' and memname like 'TT_%';

quit;

%macro test;

%do i = 1 %to &sqlobs;

%let dd=%scan(&name,&i,/);

ods excel file="C:\data\&dd..xlsx";

proc print data=ⅆ

run;

%end;

ods excel close;

%test;

Thanks,

Jag

Thanks,
Jag
Esteemed Advisor
Posts: 6,695

Re: save datasets to excel

To create a single file with sheets, you can use ods tagsets.excelxp. Be aware, though, that this will create very large .xml files when exporting larger tables (more than a few hundred lines).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 6,364

Re: save datasets to excel

Use a libname and PROC COPY.  Not sure if it is any "faster" but it is less typing.

LIBNAME OUT XLSX "C:\data\tt_xxxxxx.xlsx" ;

proc copy inlib=work outlib=out ;

select tt_: ;

run;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 276 views
  • 3 likes
  • 4 in conversation