If you are familiar with R or python
Here is a related post where I read an XLSM excel workpbook and appen some data to a worksheet.You don't need SAS Access to PC-Files. The free WPS express allows you to export un limited R dataframes or matrices to SAS datasets.
/* T0099650 Appending data to an Excel XLSM workbook
SAS Forum:
Cross posted from SAS-L
You can find a lot of information on dealing with foreign files on SAS-L
http://goo.gl/9OzDmv
https://communities.sas.com/t5/Base-SAS-Programming/export-large-datasets-to-xlsm-file/m-p/294316
HAVE (XLSM formatted Excel file)
1
2
3
SHEET1
WANT (Append 4)
HAVE (XLSM formatted Excel file)
1
2
3
4
SHEET1
SOLUTION
* It might be better to write to another xlsm workbook or
to write to a temp XLSM and rename it. Unless yoour code is solid
it is easy to corrupt your original XLSM;
%utl_submit_py64(%nrbquote(
from openpyxl import Workbook;
from openpyxl import load_workbook;
wb = load_workbook(filename='d:/xls/utl_xlsm_sample.xlsm', read_only=False, keep_vba=True);
/* grab the active worksheet */
ws = wb.active;
/* Data can be assigned directly to cells */
ws['A4'] = 88;
wb.save('d:/xls/utl_xlsm_sample.xlsm');
));
SAS MACRO
%macro utl_submit_py64(pgm)/des="Semi colon separated set of py commands";
* write the program to a temporary file;
filename py_pgm "%sysfunc(pathname(work))/py_pgm.py" lrecl=32766 recfm=v;
data _null_;
length pgm $32755 cmd $255;
file py_pgm ;
pgm="&pgm";
semi=countc(pgm,';');
do idx=1 to semi;
cmd=cats(scan(pgm,idx,';'));
if cmd=:'.' then cmd=substr(cmd,2);
put cmd $char96.;
putlog cmd $char96.;
end;
run;
%let _loc=%sysfunc(pathname(py_pgm));
%put &_loc;
filename rut pipe "C:\Python_27_64bit/python.exe &_loc";
data _null_;
file print;
infile rut;
input;
put _infile_;
run;
filename rut clear;
filename py_pgm clear;
%mend utl_submit_py64;
... View more
