Encrypting Excel Through SAS using PKzip utility

[dineshg1984]

Hi All,

Need a small assistance. I am creating a dataset and exporting them as an excell file. Then I zip and encrypt them through PKzip manually and upload the same to library.

I am pllannnning to automate the zip & encryption part as want to zip & encrypt the file at exporting step only.

I got stuck and need some guidance here.

Thanks

DInesh

[RW9]

You would use the command line to do this.  You can send out command line strings in numerous ways, X being one of them.  The below assumes Windows operating system, and you will need to check the pkzip command line parameters to make sure:

x "wzzip <location to output zip>\<output zip>.zip -s -yc <yourfile>,xlsx";

[dineshg1984]

Hi RW9,

Thanks for responding to my query.

Actually I have schedulled my code on Crontab and it throws output at a particular location on server space as an excell. I was just trying to encrypt that excell using PKZip.

Thanks

DInesh

[Tom]

Are you running on Unix?  You need to use zip instead of pkzip.  Check your system for what commands you have and the syntax for using them.

[dineshg1984]

Hey Tom,

Actuually I am able to zip the file on unix however I also want to encrypt it with password and zip in unix dont give me option to encrypt the fille

[RW9]

Wel, then you will want to look at ways of automating the "crontab" job so as that runs the command line compression package on your output file.  I don't use this so can't help there, but normally in OS scripting you run a series of commands, the last one on that list of commands should be a call to the zip exe with the parameters it needs to compress the file. 

Irrespective of process, its not a SAS task, but an operating system task as it is using a command line program to compress a file.

[dineshg1984]

Hi,

Lets forget about zipping the file. Is it possible to password protect my excel that i am exporting.

I am running this sas code as cronjob which save the output to a folder at server. Since the file is quite heavy so itt would be a time consuming effort to get the file on my local system and passwork protect  it and then upload it back.

I was just wondring to automate the stuff to save myself some time

Thanks

DG

[RW9]

Wouldn't have thought so.  If you create the output file via one of the tagsets, then it is XML output - i.e. plain text which Excel can read and interpret.  As I have said above, you will need to call some third party commandline tool to process the file, and encrypt it.  Just searching on Google yields the same information:

http://askubuntu.com/questions/17641/create-encrypted-password-protected-zip-file

Here is 7zip, one of the most popular zip programs, and it has a command line action:

https://sevenzip.osdn.jp/chm/cmdline/