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

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

NKormanik — Sat, 12 Oct 2013 00:13:32 GMT

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

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

Patrick — Sat, 12 Oct 2013 01:04:21 GMT

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;

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

NKormanik — Sat, 12 Oct 2013 04:51:01 GMT

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?

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

Patrick — Sat, 12 Oct 2013 08:31:11 GMT

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;

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

NKormanik — Sat, 12 Oct 2013 08:47:31 GMT

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.

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

NKormanik — Sat, 12 Oct 2013 09:00:59 GMT

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.

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

Patrick — Sat, 12 Oct 2013 09:40:02 GMT

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

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

NKormanik — Sat, 12 Oct 2013 22:57:34 GMT

Hmmm. Not quite, apparently:

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

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

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

Tom — Sun, 13 Oct 2013 00:10:52 GMT

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;

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

Patrick — Sun, 13 Oct 2013 09:58:39 GMT

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.

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

NKormanik — Sun, 13 Oct 2013 19:44:33 GMT

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!

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

Patrick — Tue, 15 Oct 2013 10:33:35 GMT

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

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

NKormanik — Tue, 15 Oct 2013 19:23:56 GMT

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.

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

art297 — Tue, 15 Oct 2013 19:36:18 GMT

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

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

Patrick — Wed, 16 Oct 2013 07:51:21 GMT

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.

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

NKormanik — Wed, 16 Oct 2013 08:54:07 GMT

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?

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

NKormanik — Wed, 16 Oct 2013 21:45:11 GMT

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.

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

Reeza — Wed, 16 Oct 2013 21:56:36 GMT

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?

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

NKormanik — Wed, 16 Oct 2013 22:02:28 GMT

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.

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

Reeza — Wed, 16 Oct 2013 22:35:04 GMT

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