Help using Base SAS procedures

Batch convert (export) a set of SAS data files to Excel format

Accepted Solution Solved
Reply
Regular Contributor
Posts: 223
Accepted Solution

Batch convert (export) a set of SAS data files to Excel format

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


Accepted Solutions
Solution
‎10-16-2013 06:42 PM
Super User
Super User
Posts: 7,042

Re: Batch convert (export) a set of SAS data files to Excel format

Posted in reply to NicholasKormanik

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;

View solution in original post


All Replies
Respected Advisor
Posts: 4,173

Re: Batch convert (export) a set of SAS data files to Excel format

Posted in reply to NicholasKormanik

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;

Regular Contributor
Posts: 223

Re: Batch convert (export) a set of SAS data files to Excel format

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?

Respected Advisor
Posts: 4,173

Re: Batch convert (export) a set of SAS data files to Excel format

Posted in reply to NicholasKormanik

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;

Regular Contributor
Posts: 223

Re: Batch convert (export) a set of SAS data files to Excel format

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.

Regular Contributor
Posts: 223

Re: Batch convert (export) a set of SAS data files to Excel format

Posted in reply to NicholasKormanik

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.

Respected Advisor
Posts: 4,173

Re: Batch convert (export) a set of SAS data files to Excel format

Posted in reply to NicholasKormanik

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

Regular Contributor
Posts: 223

Re: Batch convert (export) a set of SAS data files to Excel format

Hmmm.  Not quite, apparently:

ERROR: Variable memtype is not on file SASHELP.VSTABLE.

NOTE: The SAS System stopped processing this step because of errors.

Super User
Super User
Posts: 7,042

Re: Batch convert (export) a set of SAS data files to Excel format

Posted in reply to NicholasKormanik

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;

Respected Advisor
Posts: 4,173

Re: Batch convert (export) a set of SAS data files to Excel format

Posted in reply to NicholasKormanik

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.

Regular Contributor
Posts: 223

Re: Batch convert (export) a set of SAS data files to Excel format

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!

Respected Advisor
Posts: 4,173

Re: Batch convert (export) a set of SAS data files to Excel format

Posted in reply to NicholasKormanik

"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.

Regular Contributor
Posts: 223

Re: Batch convert (export) a set of SAS data files to Excel format

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.

PROC Star
Posts: 7,471

Re: Batch convert (export) a set of SAS data files to Excel format

Posted in reply to NicholasKormanik

: It would be useful, at this point, if you post the full set of code that you are running.

Regular Contributor
Posts: 223

Re: Batch convert (export) a set of SAS data files to Excel format

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.

🔒 This topic is solved and locked.

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

Discussion stats
  • 21 replies
  • 3452 views
  • 3 likes
  • 5 in conversation