In my primary working folder are 1000 .sas7bdat data files.
C:\Downloads which is also libref Nicholas
I would like to batch convert these into Excel format. Resulting converted/exported files should retain the filename, but have the .xls extension. The converted files should be placed in the same primary working folder as the original files.
Below is how I've been exporting single files:
proc export data=nicholas.wxyz
outfile= "C:\Downloads\wxyz.xls"
dbms=xls label replace;
putnames=yes;
run;
If it can be done, would someone please help with the SAS code to accomplish it?
Thanks much. Your help is greatly appreciated.
Nicholas Kormanik
So the ORIGINAL solution should do what you want, with trivial changes.
Here is a summary of the changes I made.
1) Eliminate the selection criteria used to identify which SAS datasets to export to SAS. Instead it will export them all.
2) Eliminate the SUBSTR() function call that was removing the first letter of the database name.
3) Added macro variables OUTPATH and INPATH to have an explicit location to set the source and the target directory names for reading the SAS datasets and writing the EXCEL files. If you want them in the same directory you could eliminate one of the macro variables or just set them both to the same value.
4) Added an explicit LIBNAME statement. Note that the libref used (SOURCE) has to match the value used in the WHERE clause to select the list of tables to export.
5) Added %NRSTR() into the string passed to CALL EXECUTE to avoid possible timing issues caused by pushing macro calls onto the execute stack.
%macro export(inlib,intbl,outpath,outfile);
proc export data=&inlib..&intbl
outfile= "&outpath\&outfile..xls"
dbms=xls label replace;
putnames=yes;
run;
%mend;
%let outpath=c:\excel_files;
%let inpath=c:\sas_files;
libname source "&inpath";
data _null_;
set sashelp.vstable (where=(libname='SOURCE'));
call execute(cats('%nrstr(%export)('
,catx(',',libname,memname,'&outpath',memname)
,')'));
run;
Some code like below should do:
%macro export(inlib,intbl,outpath,outfile);
proc export data=&inlib..&intbl
outfile= "&outpath\&outfile..xls"
dbms=xls label replace;
putnames=yes;
run;
%mend;
data _null_;
set sashelp.vstable (where=(libname='NICHOLAS' and prxmatch('/^_\d+ *$/',memname)>0));
length cmd $ 1000;
outpath='C:\Downloads';
cmd=cats('%export(',libname,',',memname,',',outpath,',',substrn(memname,2),')');
call execute(cmd);
run;
Thanks for trying, Patrick. I placed several test SAS data files into C:\Downloads and tried your code. Unfortunately..., no go:
NOTE: There were 0 observations read from the data set SASHELP.VSTABLE.
WHERE (libname='Nicholas') and (PRXMATCH('/^_\d+ *$/', memname)>0);
Any further ideas?
Hi Nicholas
I've actually tested the code and it worked for me. If it's not working for you then your real data is different from what you've told us so far (eg. your source tables don't follow a naming pattern of 1 underscore and then at least 1 digit and no other characters).
Below the full code I've used for testing.
libname nicholas (work);
data nicholas._00001 nicholas._00003 nicholas._00005 nicholas._00x4;
set sashelp.class;
run;
%macro export(inlib,intbl,outpath,outfile);
proc export data=&inlib..&intbl
outfile= "&outpath\&outfile..xls"
dbms=xls label replace;
putnames=yes;
run;
%mend;
data _null_;
set sashelp.vstable (where=(libname='NICHOLAS' and prxmatch('/^_\d+ *$/',memname)>0));
length cmd $ 1000;
outpath='C:\Downloads';
cmd=cats('%export(',libname,',',memname,',',outpath,',',substrn(memname,2),')');
call execute(cmd);
run;
libname nicholas clear;
Perhaps the problem I had was in the naming of the files. The test files I used had different names from the example I gave at the outset. Sorry for that confusion.
I was assuming in my original question that any filename would work with the code.
By the way, if it wasn't clear..., the SAS data files are being held within the folder C:\Downloads (which is also libref Nicholas).
That would be the same location I'd like to place the converted/exported files.
I tried the code you used in your reply, and indeed it worked fine.
Could the code be generalized so that any SAS data filename would work?
Thanks.
All information you need is in sashelp.vstable. You simply change the where clause to select a different set of tables.
And to never create duplicates I probably would use a naming convention for the excel files which includes the libref and the full table name, so something like:
cmd=cats('%export(',libname,',',memname,',',outpath,',',cats(libname,'_',memname),')');
To select all tables under library NICHOLAS:
set sashelp.vstable (where=(libname='NICHOLAS' and memtype='DATA'));
Hmmm. Not quite, apparently:
ERROR: Variable memtype is not on file SASHELP.VSTABLE.
NOTE: The SAS System stopped processing this step because of errors.
VSTABLE and VTABLE are two different views.
899 proc sql;
900 describe view sashelp.vstable;
NOTE: SQL view SASHELP.VSTABLE is defined as:
select libname, memname
from DICTIONARY.MEMBERS
where memtype = 'DATA'
order by libname asc, memname asc;
901 describe view sashelp.vtable;
NOTE: SQL view SASHELP.VTABLE is defined as:
select *
from DICTIONARY.TABLES;
Thanks Tom - should have checked. So Nicholas: No need for "memtype='DATA' " as VSTABLE only shows tables and nothing else.
And: You are allowed to do some debugging your own if something doesn't work as posted.
Fantastic. It works.
Only minor problem is that the code causes a truncation of the first character of the filename. I wonder if you can fix what's causing that?
Example:
_00001.sas7bdat ---> 00001.xls
data.sas7bdat ---> ata.xls
Thanks!
"Only minor problem is that the code causes a truncation of the first character of the filename."
That's what you've asked for (implicitly) in your original post with the sample data you've provided. It's a very easy code change to my original post - and then there is also a follow up post about the filename.
I suggest you give it a go and try to apply this small change on your own. Let us know in case you get really stuck with it.
In the original question I did say, "Resulting converted/exported files should retain the filename, but have the .xls extension."
I haven't a clue how to fix the truncation......
All Greek to me.
Perhaps I should concisely restate the objective:
User wants to convert all the SAS data files in folder XYZ into Excel format.
Any number of files. Whatever the file names. Maintain file names, only changing the extension in created output.
I feel this problem is pretty basic, in that many people around the globe would like to be able to easily do this. When they do an Internet search for the answer..., I hope they will come upon this thread, and happily find the answer. This all seems a lot more complicated than it should be.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.