topic Re: Howto: copy excel file using infile/input and file/put in SAS Programming

Howto: copy excel file using infile/input and file/put

GertNissen — Wed, 07 Jul 2010 08:53:07 GMT

I have asked for this once, but havent found an answer.

I wan't to find a method to copy an Excel file from one location til another using a datastep with infile/input and file/put statements.

I'm trying to learn how to do this using SAS and not by using OS copy or similar.

I'm looking for something like
[pre]
filename input;
filename output;

data _null_;
infile input;
input;
file output;
put _infile_;
run;
[/pre]
This code will not work for an Excel file (works fine with textfiles), and I need to find the proper options etc.

Re: Howto: copy excel file using infile/input and file/put

Patrick — Wed, 07 Jul 2010 10:05:33 GMT

May I ask why you want to do this?

Is it because shell escapes are disables in your environment as this is the default setting for EGuide (noxcmd)?

If shell escapes are possible then I would recommend you use OS copy.

Re: Howto: copy excel file using infile/input and file/put

GertNissen — Wed, 07 Jul 2010 10:50:22 GMT

Hi Patrick

I'm not using EG and I can easily solve the problem using x command, call system or simlilar. (sorry, should have stated that in my question)

I was just wondering how I could read a binary file and write it back using only datastep statements. I'm curious and would like to try it 😉 but failed ;-(

I have somewhat similar problem (batch job) where I want to write a file (Excel) to the webdav (overwrite). If you have a solution for this, I'm also interessted 😉

Re: Howto: copy excel file using infile/input and file/put

darrylovia — Wed, 07 Jul 2010 12:03:42 GMT

You could use the CALL SYSTEM function from within a DATA STEP.

Or you can utilize SAS's abiltiy to call OS DLLs by using the SASCBTBL attribute table with the MODULE family of functions and call routines. I have just finished writing a paper for SESUG and NESUG regarding this topic. My install of SAS is EG and the X command and SYSTASK functions are turned off, so the SASCBTBL access method works great.

The below papers are a good start.
http://www2.sas.com/proceedings/sugi30/248-30.pdf

and in the SAS Online Doc
http://support.sas.com/documentation/cdl/en/hostwin/63285/HTML/default/viewer.htm#/documentation/cdl/en/hostwin/63285/HTML/default/accdll.htm

Darryl

Re: Howto: copy excel file using infile/input and file/put

GertNissen — Wed, 07 Jul 2010 21:59:20 GMT

HI Darryl

I'm not using EG and I can easily solve the problem using the X command, CALL SYSTEM or simlilar. (sorry, should have stated that more clearly in my first post)

My curiosity was regarding the opportunity to use the DATA STEP statements infile/input and file/put to 'copy' an Excel file.

Re: Howto: copy excel file using infile/input and file/put

darrylovia — Thu, 08 Jul 2010 11:14:51 GMT

If I'm not mistaken since Excel is a binary file type the INFILE/FILE/PUT statements will not work. SAS is expecting a text type file looking for end of record markers etc...

Darryl

Re: Howto: copy excel file using infile/input and file/put

GertNissen — Thu, 08 Jul 2010 12:18:29 GMT

Yes, Excel files are binary files. I will not read the Excel file to extract data

With DATA _null_; You can read/write everything with infile/file and I was hoping thath RECFM=S and nbyte= could be used, in combination with put _infile_ $varying. len; like below....

I Have found these resources, but no breakthrough yet.

Usage Note 6588: Serving binary files when running the IntrNet Application Server
- http://support.sas.com/kb/6/588.html
Problem Note 3404: The "_INFILE_" INPUT/PUT statement option is inconsistent with binary mode I/O
- http://support.sas.com/kb/3/404.html
Writing MS Office and other binary files
- http://support.sas.com/forums/thread.jspa?threadID=5735

[pre]
data _null_;
length char $1;
infile dav length=len lrecl=32767 nbyte=nbyt;
nbyt = -1;
input;
file target lrecl=32767;
do i = 1 to len;
char = substr(_infile_,i,1);
put char $char1. @@;
end;
run;
[pre]

Re: Howto: copy excel file using infile/input and file/put

data_null__ — Thu, 08 Jul 2010 13:35:14 GMT

Test the following.

I used the DOS COMP command to compare the files, they compare OK.
[pre]
comp testcopy.xls testcopy02.xls
Comparing testcopy.xls and testcopy02.xls...
Files compare OK
[/pre]

And the copy opens as expected in EXCEL. But after opening the copy, but not saving, the compare changes.

[pre]
Comparing testcopy.xls and test
Compare error at OFFSET 666C
file1 = A0
file2 = C0
Compare error at OFFSET 666D
file1 = 8D
file2 = 9D
Compare error at OFFSET 666E
file1 = 32
file2 = 4
Compare error at OFFSET 666F
file1 = 2E
file2 = FC
Compare error at OFFSET 6670
file1 = 90
file2 = A1
[/pre]

TESTCOPY.XLS is just XLS with several sheets in binary EXCEL format.

[pre]
filename FT33F001 'testcopy.xls' recfm=N;
filename FT34F001 'testcopy02.xls' recfm=N;
data _null_;
infile FT33F001;
file FT34F001;
input str $256.;
put str $256.;
run;
[/pre]

Message was edited by: data _null_; Message was edited by: data _null_;

Re: Howto: copy excel file using infile/input and file/put

Tim_SAS — Thu, 08 Jul 2010 14:14:49 GMT

If I had to guess, I'd guess that the Excel file has an embedded date or time value that changes when the file is opened and that's the difference COMP is finding.

Re: Howto: copy excel file using infile/input and file/put

GertNissen — Thu, 08 Jul 2010 14:48:59 GMT

Hi _null_

A big Tx to you 😉

I got it to work with $32767., if I use $256. I can't open the new copy (Excel reports that its damaged). I don't know why ?
[pre]
filename FT33F001 'D:\temp\test.xls' recfm=N;
filename FT34F001 'D:\temp\testcopy.xls' recfm=N;

data _null_;
infile FT33F001 lrecl=32767;
file FT34F001 lrecl=32767;
input str $32767.;
put str $32767.;
run;
[/pre]

Re: Howto: copy excel file using infile/input and file/put

data_null__ — Thu, 08 Jul 2010 16:00:00 GMT

I did a bit more testing. with a larger XL file. I believe more care must be taken with the last record that is potentially less that LRECL. See modified version of the program, with I think works as we want.

[pre]
%let lrecl=32767;
%let lrecl=4096;
filename FT33F001 'testcopy01.xls' recfm=N lrecl=&lrecl;
filename FT34F001 'testcopy02.xls' recfm=N lrecl=&lrecl;
data _null_;
infile FT33F001 length=length;
file FT34F001;
input str $char&lrecl..;
if length ne &lrecl then putlog 'NOTE: ' (_n_ length)(=);
put str $varying&lrecl.. length;
run;
[/pre]

Re: Howto: copy excel file using infile/input and file/put

Peter_C — Thu, 15 Jul 2010 14:27:54 GMT

you almost made it!
You just have to deal with two issues
1 : to ignore line ending characters (like crlf), use recfm=F (after all it is a binary file)
2 : to ignore embedded EOF marks in binary files, use "ignoreDOSeof" infile option.
Here is your code , just extended a little [pre]filename input "%sysget(temp)\tes2.xls" ;
filename output "%sysget(temp)\tes3.xls" ;
data _null_;
infile input recfm=f ignoreDOSeof ;
file output recfm=f ;
input ;
put _infile_ ;
run;
dm 'winexecfile "%path(output)" ';[/pre]
It is possible that I am successful only because my file is a multiple of 256 bytes (the default lrecl= value). If you are concerned, add LRECL=1 to the infile and file options. For my 4 megabyte test workbook, it took my small machine just 2.7 secs real time to handle with LRECL=1 instead of 0.6 secs for the default LRECL=256.

PeterC

Re: Howto: copy excel file using infile/input and file/put

hamish_at_nab — Thu, 14 Jul 2016 02:20:37 GMT

sorry to bump this as just wanted to add what I found that finally worked for me in similar circumstances:

http://support.sas.com/kb/20/784.html

 data _null_;
    length data $1;
    INFILE  in_filename  recfm=n;
    file  out_filenmae recfm=n mod;
    input data  $char1. @@;
    put data $char1. @@;
 run;

Although this is written for STP web process it should apply when writting to another file as the _webout is similar. The secret seems to be the recfm=n and the @@s.