BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
NKormanik
Barite | Level 11

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

21 REPLIES 21
Patrick
Opal | Level 21

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;

NKormanik
Barite | Level 11

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?

Patrick
Opal | Level 21

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;

NKormanik
Barite | Level 11

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.

NKormanik
Barite | Level 11

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.

Patrick
Opal | Level 21

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

NKormanik
Barite | Level 11

Hmmm.  Not quite, apparently:

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

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

Tom
Super User Tom
Super User

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;

Patrick
Opal | Level 21

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.

NKormanik
Barite | Level 11

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!

Patrick
Opal | Level 21

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

NKormanik
Barite | Level 11

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.

art297
Opal | Level 21

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

NKormanik
Barite | Level 11

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

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