I am trying to create a read-only Excel file from SAS. I have found a lot of methods that will do the trick, but I can't seem to get any of them to work.
I have tried using the X commands:
x 'attrib +r "S:\cdm\Programming\OutputFile_&sysdate9..xlsx"';
I tried r+ and +r with no luck.
I have tried the protect_worksheet='no' option in ODS EXCEL with no luck.
I have tried applying at password as found here: https://support.sas.com/kb/31/328.html with no luck.
I think I have narrowed it down to altering a macro that I am already using that just needs another parameter, but I don't know the VBA syntax.
%macro convert_files(default=,ext=);
data _null_;
file "'&default\temp.vbs'";
put "set xlapp = CreateObject(""Excel.Application"")";
put "set fso = CreateObject(""scripting.filesystemobject"")";
put "set myfolder = fso.GetFolder(""&default"")";
put "set myfiles = myfolder.Files";
put "xlapp.DisplayAlerts = False";
put " ";
put "for each f in myfiles";
put "If f.Size < 70000 And fso.GetExtensionName(f)=""&ext"" Then";
put " f.Delete True";
put " End If";
put " Next";
put " ";
put "for each f in myfiles";
put " ExtName = fso.GetExtensionName(f)";
put " Filename= fso.GetBaseName(f)";
put " if ExtName=""&ext"" then";
put " set mybook = xlapp.Workbooks.Open(f.Path)";
put " xlapp.Visible = false";
put " mybook.SaveAs ""&default.\"" & Filename & "".xlsx"", 51";
put " End If";
put " Next";
put " mybook.Close";
put " xlapp.DisplayAlerts = True";
/* Removes original files */
put " FSO.DeleteFile(""&default\*.&ext""), DeleteReadOnly";
put " xlapp.Quit";
put " Set xlapp = Nothing";
put " strScript = Wscript.ScriptFullName";
put " FSO.DeleteFile(strScript)";
run;
x "cscript ""&default\temp.vbs""";
%mend;
This macro is what we use to convert XML files to XLSX. There is a SaveAs line in the macro where I specify the filename and the file type. After that is supposed to be allowance to specify a password, backup, and read-only option, but I don't know how to alter it to get what I want. Does anyone have any suggestions for my alteration or a different way to make a file read only?
Hi djbateman,
You can define your file permissions in the filename statement:
filename f "&default\temp.vbs" permission='A::u::rwx,A::g::r-x,A::o::---';
Then use in your data step as
data _null_;
file f;
. . .
For more information on assigning files permissions programmatically see my blog post Let SAS write batch scripts for you.
Hope this helps.
Hi djbateman,
You can define your file permissions in the filename statement:
filename f "&default\temp.vbs" permission='A::u::rwx,A::g::r-x,A::o::---';
Then use in your data step as
data _null_;
file f;
. . .
For more information on assigning files permissions programmatically see my blog post Let SAS write batch scripts for you.
Hope this helps.
How does this work under windows? There are no group and other users.
There are groups in Windows. I assume that OTHER is not the USER and not a GROUP where the USER belongs to. I have not played with this on Windows though. Here is the link to the FILENAME PERMISSION= option documentation for Windows .
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: