topic Writing MS Office and other binary files in Microsoft Integration with SAS

Writing MS Office and other binary files

ErikT — Mon, 04 May 2009 21:54:17 GMT

Hi,

At my presentation on using the DATA Step as a POP3 mail client somebody asked how to deal with attachments. For text-based attachments that is no problem. Binary files are more complicated. First you have to decode them (back from Base64 to binary) and then file them. Decoding is a piece of cake, but the filing part is giving me a headache. I could not open it.

Then I tried to read an Excel file (*.xls) and write it out again, just with INPUT; PUT _INFILE_; I could not open the copied file, although in the properties it looks identical (at least in byte count).
Next I did a comparison by reading the original file and the copy file and compare them byte by byte. No difference to see.

So I assume that there are certain hidden elements in those binary files that are not read or rewritten in this process.

Any suggestion what I might be missing and how to solve it?

Re: Writing MS Office and other binary files

Bill — Tue, 05 May 2009 14:01:25 GMT

please post your code ...

Re: Writing MS Office and other binary files

ErikT — Tue, 05 May 2009 15:17:32 GMT

This is the code I used for copying:

filename orig 'e:\sgf2009\test1.xls' recfm=v lrecl=32767 ignoredoseof;
filename copy 'e:\sgf2009\test2.xls' recfm=v lrecl=32767 ignoredoseof;
data _null_;
infile orig ;
input;
file copy;
put _infile_;
run;
filename orig clear;
filename copy clear;

I think the problem is that the filename statement does not handle the file attribute settings under windows and I don't see any options by which I could set them.
Although the contents of the files is identical, the properties windows are not. The original shows 3 tabs (General, Custom, Summary), the copy shows only one (General)

rgds Erik

Re: Writing MS Office and other binary files

Bill — Tue, 05 May 2009 15:57:46 GMT

how about

data _null_;
call system ('copy c:\temp\Orig.xls c:\temp\Copy.xls');
run;

?

Re: Writing MS Office and other binary files

ErikT — Tue, 05 May 2009 17:43:42 GMT

Yes, but that does not solve my problem.

The final step is not the copy, that was only a test, trying to figure out what went wrong.

The idea is to decode and file a BASE64 mail attachment. So I need to do it in a DATA step.

Re: Writing MS Office and other binary files

Bill — Wed, 06 May 2009 13:31:09 GMT

and this option doesn't work for you?

attach=("c:\temp\test.xls"
ct="application/octet-stream");

Re: Writing MS Office and other binary files

ErikT — Sun, 10 May 2009 21:56:43 GMT

Bill,

I have given up on finding out why the copying did not work, but I have been able to reach my original goal: converting a BASE64 encoded file into its original binary status.

If you want to do it in a DATA step, with FILE and PUT statements, the trick is that you have to write them as one continuous data stream. To do that you define a logical record length which is bigger than the biggest attachment you are handling. I tried LRECL of more than 1 MB and you use a trailing @ in the PUT statement.

The alternative is to use an external program like base64.exe and use a CALL SYSTEM to execute it.

Thanks to your interest in the matter!