topic Re: Encrypting data in excel file from SAS dataset in SAS Programming

Encrypting data in excel file from SAS dataset

Yuriy2301 — Tue, 19 Oct 2010 14:00:10 GMT

Hi,

does somebody can help with next issue:
I need to encrypt data when exporting data from SAS dataset to excel file,
then I send this file with FILENAME EMAIL statement, that's why I need data to be encrypted.
Or maybe there is another way to encrypt data for sending through email?
Remark: creating excel files and sending them to email are doing programmatically, so that method of encryption should also working on similiar way.

Thanx

Re: Encrypting data in excel file from SAS dataset

Cynthia_sas — Tue, 19 Oct 2010 16:25:52 GMT

Hi:
You might want to open a track with Tech Support on this question. When you export a SAS dataset to Excel, you are generally using PROC EXPORT. I do not believe there is a way to force PROC EXPORT to encrypt the data -after- it has gone to Excel.

If you are using ODS HTML or ODS TAGSETS.EXCELXP or ODS CSV to send procedure output to Excel -- again, I do not believe there is a way to encrypt the SAS output (HTML, XML or CSV) once it has been created.

There are some new system options in SAS 9.2, as described in this document:
http://support.sas.com/documentation/cdl/en/secref/62092/PDF/default/secref.pdf

But they don't seem quite relevant to your question -- which is about controlling encryption after SAS has created a file.

To open a track with Tech Support, fill out the form at this link:
http://support.sas.com/ctx/supportform/createForm

cynthia

Re: Encrypting data in excel file from SAS dataset

Doc_Duke — Tue, 19 Oct 2010 22:14:43 GMT

Excel's encryption is generally weak, so I would not trust it. It takes about 10 minutes to break an Excel 2003 file.

What you might want to consider is to write the Excel file to disk, then x-out from SAS to run a batch encryption with WinZip or 7Zip, and finally to use SAS to e-mail that file.

If your office supports a secure e-mail tool, you can usually get to it from the SAS e-mail (for instance, with our Tumbleweed secure e-mail server, I just have to put "(secure)" (no quotes) in the title line to get the encryption to kick in. Your local tech support may have the answer to this one.

Doc Muhlbaier
Duke

Re: Encrypting data in excel file from SAS dataset

Yuriy2301 — Wed, 20 Oct 2010 06:50:32 GMT

Hi Doc,

could you explain more detailed with "then x-out from SAS to run a batch encryption with WinZip or 7Zip"?
maybe you have some example or piece of code where similiar is doing?
Is this possible to run a batch encryption from SAS code?

Re: Encrypting data in excel file from SAS dataset

Peter_C — Wed, 20 Oct 2010 11:07:45 GMT

> then I send this file with FILENAME EMAIL statement,
> that's why I need data to be encrypted.

why make SAS work so hard ? 😉

> maybe there is another way to encrypt data for
> sending through email?

how about PGP encryption?
This supports just the message transfer and not what happens after the message is opened by the receiver..
Your system and security administrators should be able to show how it is integrated in your environment - It might need no SAS code changes.

luck
peterC

Re: Encrypting data in excel file from SAS dataset

Doc_Duke — Wed, 20 Oct 2010 13:52:41 GMT

Since I have tumbleweed, I don't have an example for you. The X command is a standard part of SAS base. You can find the syntax on support.sas.com . If you look at the 7 Zip web site, you can find the syntax for running 7 Zip in batch.

Re: Encrypting data in excel file from SAS dataset

Yuriy2301 — Thu, 21 Oct 2010 13:48:20 GMT

Hi

I tried X command and it works in SAS Base 9.1,
but in Enterprise Guide and Data Integration Studio tools (which are on the same server) it doesn't.

Maybe some ideas why so?

Re: Encrypting data in excel file from SAS dataset

Yuriy2301 — Thu, 21 Oct 2010 13:53:42 GMT

also code runs succesfully but nothing happens..

Re: Encrypting data in excel file from SAS dataset

Doc_Duke — Thu, 21 Oct 2010 15:15:40 GMT

The X command is disabled by default in EGuide. There are a number of threads in the EGuide form that discuss enabling it. I don't use DI Studio but suspect the same situation.

Re: Encrypting data in excel file from SAS dataset

JayPabla — Tue, 03 Apr 2012 14:30:45 GMT

Hi,

I'm relativly new to SAS, i have SAS 9.1 and 9.2 and would like to be able to copy your method of encryption please.

I currently use SAS to export a dataset to Excel 2003 format.

I then use DDE to open the file in Excel, do some manipulation and save and close.

I'd like to be able to use SAS to Zip the file and email it out, all within the sas code.

My business use SecureZip and not WinZip... please can you help with an example?

Thank you

Jay