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

[Reeza]

It isn't complex, but you seem to want someone else to do the work rather than modify posted code.

People have asked you to repost the code you used and claim does not work and you have not, so why should anyone invest anymore time in a question that works for everyone else?

[NKormanik]

Because I don't know how to do it.  I'm the one asking how.

At this point I'd like a clear path for others out there that search for the answer.

[Reeza]

Do you want code or instructions on how to solve the problem?

[Patrick]

You've updated/changed your initial post changing the table names. Originally they were something like "_1234" and you wanted the excel to be named "1234.xls".

Is my assumption correct that you don't know how to write SAS code? As the change to the code already posted is really simple.

[NKormanik]

Yes, , your assumption is correct.  I've looked over your code and can't see any easy way of solving the truncation issue.  So..., how is it done?

[Tom · Accepted solution]

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;

[NKormanik]

Outstanding.  Thank you!