BookmarkSubscribeRSS Feed
GertNissen
Barite | Level 11
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.
12 REPLIES 12
Patrick
Opal | Level 21
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.
GertNissen
Barite | Level 11
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 😉
darrylovia
Quartz | Level 8
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...



Darryl
GertNissen
Barite | Level 11
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.
darrylovia
Quartz | Level 8
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
GertNissen
Barite | Level 11
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]
data_null__
Jade | Level 19
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_;
Tim_SAS
Barite | Level 11
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.
GertNissen
Barite | Level 11
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]
data_null__
Jade | Level 19
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]
Peter_C
Rhodochrosite | Level 12
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
hamish_at_nab
Fluorite | Level 6

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.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 12 replies
  • 4347 views
  • 0 likes
  • 7 in conversation