If you have the COBOL copybook   check out http://support.sas.com/kb/42/449.html ... View more

For non-programmers you can mouse surf a solution using libreoffice. LibreOffice can also convert MS Office docs to PDF and append them. You only need gswin64c.exe from open source ghostscript. HAVE d:/pdf/class.pdf d:/pdf/cars.pdf WANT APPEND CARS.PDF TO CLASS.PDF d:/pdf/classcars.pdf SOLUTION ods pdf file="d:/pdf/class.pdf"; proc print data=sashelp.class; run;quit; ods pdf close; ods pdf file="d:/pdf/cars.pdf"; proc print data=sashelp.cars(obs=20); run;quit; ods pdf close; x "cd d:\pdf"; x "C:\Progra~1\gs\gs9.19\bin\gswin64c.exe -dNOPAUSE -sDEVICE=pdfwrite -sOUTPUTFILE=classcars.pdf -dBATCH class.pdf cars.pdf"; 11 ods pdf file="d:/pdf/class.pdf"; NOTE: Writing ODS PDF output to DISK destination "d:\pdf\class.pdf", printer "PDF". 12 proc print data=sashelp.class; 13 run; NOTE: There were 19 observations read from the data set SASHELP.CLASS. NOTE: PROCEDURE PRINT used (Total process time): real time 0.05 seconds user cpu time 0.00 seconds system cpu time 0.03 seconds memory 656.25k OS Memory 13796.00k Timestamp 09/13/2016 05:08:00 AM Step Count 10 Switch Count 0 13 ! quit; 14 ods pdf close; NOTE: ODS PDF printed 1 page to d:\pdf\class.pdf. 15 ods pdf file="d:/pdf/cars.pdf"; NOTE: Writing ODS PDF output to DISK destination "d:\pdf\cars.pdf", printer "PDF". 16 proc print data=sashelp.cars(obs=20); 17 run; NOTE: There were 20 observations read from the data set SASHELP.CARS. NOTE: PROCEDURE PRINT used (Total process time): real time 0.07 seconds user cpu time 0.07 seconds system cpu time 0.00 seconds memory 1186.53k OS Memory 14564.00k Timestamp 09/13/2016 05:08:01 AM Step Count 11 Switch Count 0 17 ! quit; 18 ods pdf close; NOTE: ODS PDF printed 2 pages to d:\pdf\cars.pdf. 19 x "cd d:\pdf" 19 ! ; 20 x "C:\Progra~1\gs\gs9.19\bin\gswin64c.exe -dNOPAUSE -sDEVICE=pdfwrite -sOUTPUTFILE=classcars.pdf -dBATCH class.pdf cars.pdf" 20 ! ; ... View more

Maybe in 9.4M3 the start_at ODS option will work, but for now you can sort of do it with SAS and R. Not all formatting is copied. HAVE (TWO EXCEL SHEETS) EXCEL A B C ROW ---------|-----------|--------------- 1 Country Product Actual Sales 2 CANADA BED $47,729.00 3 CHAIR $50,239.00 4 DESK $52,187.00 5 SOFA $50,135.00 6 TABLE $46,700.00 7 GERMANY BED $46,134.00 8 CHAIR $47,105.00 9 DESK $48,502.00 ------ SHEET1 ------ EXCEL A B C ROW ---------|-----------|--------------- Predicted 1 Country Product Sales 2 CANADA BED $44,215.00 3 CHAIR $46,796.00 4 DESK $49,393.00 5 SOFA $45,726.00 6 TABLE $46,889.00 7 GERMANY BED $43,796.00 8 CHAIR $44,069.00 9 DESK $44,639.00 SOFA $49,517.00 ------ SHEET2 ------ WANT THE REPORTS TO BE SIDE BY SIDE IN SHEET3 (We could hvae added sheet1 to sheet2 but it is better to create sheet3 and drop sheet1 and 2. EXCEL A B C F G H ROW ---------|-----------|--------------- ---------|-----------|--------------- Predicted 1 Country Product Actual Sales Country Product Sales 2 CANADA BED $47,729.00 CANADA BED $44,215.00 3 CHAIR $50,239.00 CHAIR $46,796.00 4 DESK $52,187.00 DESK $49,393.00 5 SOFA $50,135.00 SOFA $45,726.00 6 TABLE $46,700.00 TABLE $46,889.00 7 GERMANY BED $46,134.00 GERMANY BED $43,796.00 8 CHAIR $47,105.00 CHAIR $44,069.00 9 DESK $48,502.00 DESK $44,639.00 SOFA $49,517.00 SHEET1 SOLUTION * CREATE TWO SAS REPORTS %utlfkil(d:/xls/class1.xlsx); ods excel file="d:/xls/class1.xlsx"; ods excel options(sheet_name="sheet1" start_at="A1"); proc report data=sashelp.prdsale; column country product actual; define country / group; define product / group; rbreak after / summarize; run;quit; * you cannot output to the same sheet - ignores sheet1 and puts the report in sheet2 (9.4M2); * I believe this is supposed to work in SAS 9,4M3?; * Does not work in 9.4M2; ods excel options(sheet_name="sheet1" start_at="A1"); proc report data=sashelp.prdsale ; column country product predict; define country / group; define product / group; rbreak after / summarize; Run; ods excel close; * I believe this is supposed to work in SAS 9,4M3?; * Does not work in 9,4M2; HERE IS A R SOLUTION; %utl_submit_r64(' library(XLConnect); wb <- loadWorkbook("d:/xls/class1.xlsx"); createSheet ( wb , "sheet3" ); prcntg <- createCellStyle(wb); setDataFormat(prcntg, format = "$00,000.00"); sheet1 = readWorksheet(wb, sheet = getSheets(wb)[1]); sheet2 = readWorksheet(wb, sheet = getSheets(wb)[2]); writeWorksheet(wb,sheet1,sheet="sheet3",startCol=1,header=T); writeWorksheet(wb,sheet2,sheet="sheet3",startCol=6,header=T); setCellStyle(wb, sheet = "sheet3", row = 1:17, col = 3, cellstyle = prcntg); setCellStyle(wb, sheet = "sheet3", row = 1:17, col = 8, cellstyle = prcntg); saveWorkbook(wb,"d:/xls/class1.xlsx"); '); 1. load existing workbook 2. create formats for sales and predicted sales 3. load sheet1 and sheet2 4. Place sheet1 and sheet2 intoo sheet3 ... View more

  I posted what I think is a more comprehensive 'R' based solution. This solution should map easly to IML.   https://listserv.uga.edu/cgi-bin/wa?A2=SAS-L;a2483999.1609b ... View more

Not sure this helps Here I use SAS and R to place 'arbitrary' formulas into excel. The BMI column is computed from previous columns using excel formulas. You can also modify existing excel template with the libname and connection engines. SAS/R: Adding formulas to an existing ODS excel workbook with two sheets. related post http://goo.gl/u7PBe8 https://communities.sas.com/t5/ODS-and-Base-Reporting/SAS-Excel-Plug-in-add-queries-to-cell/m-p/296045 for R updated workbook https://drive.google.com/open?id=0ByX2ii2B0Rq9enpkMkk4MS1Fd0U There appears to be an issue with WPS when you need to use a mixture of 32 and 64bit R. If you download the combined 32/64 bit R then R seems to seemlessly move as needed from 32 to 64bit addressing and packages. WPS only appears to support 32 or 64bit but not a combined installation. I was unable to get this to work in WPS. This works with both XLS xnd XLSX seemlessly. Howver the XLS seems an order of magnitude faster, Those wordy zipped XML files take time? HAVE EXCEL WORKBOOK WITH TWO SHEETS (JOHN row is highlighted in yellow) Up to 40 obs from xel.class total obs=19 SHEET STOCKS SHEET CLASS ----------------------- -------------------------------------- | EXCEL A B | | EXCEL A D E | | | | | | ROW STOCK DATE | | ROW NAME HEIGHT WEIGHT | | | | | | 1 IBM 01DEC05 | | 1 Alfred 69.0 112.5 | | 2 IBM 01NOV05 | | 2 Alice 56.5 84.0 | | 3 IBM 03OCT05 | | 3 Barbara 65.3 98.0 | | 4 IBM 01SEP05 | | 4 Carol 62.8 102.5 | | 5 IBM 01AUG05 | | 5 Henry 63.5 102.5 | | 6 IBM 01JUL05 | | 6 James 57.3 83.0 | | 7 IBM 01JUN05 | | 7 Jane 59.8 84.5 | | 8 IBM 02MAY05 | | 8 Janet 62.5 112.5 | | 9 IBM 01APR05 | | 9 Jeffrey 62.5 84.0 | | 10 IBM 01MAR05 | | 10 John 59.0 99.5 | ----------------------- -------------------------------------- CLASS | STOCKS WANT Add the column BMI and the sum of height in place Using the existing ODS excel output(create=false in R). Also preserve formatting. The ods exel 'file=' always overwrites the previous workbook? You can't add sheets or columns? However the libname and connection engines doe provide some modification capability. DDE is not always stable, it depends on what 'connections the OS is using. SHEET STOCKS SHEET CLASS ----------------------- --------------------------------------------------- | EXCEL A B | | EXCEL A D C D | | | | | | ROW STOCK DATE | | ROW NAME HEIGHT WEIGHT BMI | | | | | | 1 IBM 01DEC05 | | 1 Alfred 69.0 112.5 16.61153119 | | 2 IBM 01NOV05 | | 2 Alice 56.5 84.0 18.49855118 | | 3 IBM 03OCT05 | | 3 Barbara 65.3 98.0 16.15678844 | | 4 IBM 01SEP05 | | 4 Carol 62.8 102.5 18.27089841 | | 5 IBM 01AUG05 | | 5 Henry 63.5 102.5 17.87029574 | | 6 IBM 01JUL05 | | 6 James 57.3 83.0 17.77150358 | | 7 IBM 01JUN05 | | 7 Jane 59.8 84.5 16.61153119 | | 8 IBM 02MAY05 | | 8 Janet 62.5 112.5 20.2464 | | 9 IBM 01APR05 | | 9 Jeffrey 62.5 84.0 15.117312 | | 10 IBM 01MAR05 | | 10 John 59.0 99.5 | ----------------------- | | | 1900.5 | --------------------------------------------------- SOLUTION * create a workbook and sheet class; ods listing close; ods excel file="d:/xls/formulas.xlsx" style=minimal; ods excel options(sheet_name="class" sheet_interval="none"); proc report data=sashelp.class nowd; columns name height weight ; define name / style(column)={font_weight=bold}; define height / display; define weight / display; compute name; if name = 'John' then call define(_row_, "Style", "Style = [background = yellow]"); endcomp; run;quit; * add sheet stocks; ods excel options(sheet_name="stocks" sheet_interval="none"); proc print data=sashelp.stocks(obs=10 drop=volume adjclose); run;quit; ods excel close; ods listing; %utl_submit_r64(' library(XLConnect); wb <- loadWorkbook("d:/xls/formulas.xlsx",create = FALSE); box<-getBoundingBox(wb, sheet="class"); box; colname <- readWorksheet(wb, sheet="class", startRow = box[1,], startCol = box[1,], endRow = box[1,], endCol = box[4,]); colname; colIndex <- which(names(colname) == "WEIGHT"); colIndex; rowIndex <- box[3,1]; rowIndex; input <- paste(idx2cref(c(box[1,]+1, colIndex, rowIndex, colIndex)), collapse=":"); input; formula <- paste("SUM(", input, ")", sep=""); formula; setCellFormula(wb, "class", rowIndex + 1, colIndex, formula); rows<-c((box[1,]+1):rowIndex); rows; rowchr<-as.character(rows); s<-idx2aref(c((box[1,]+1), colIndex, (box[1,]+1), colIndex-1)); fml<-paste(substr(s, 1, 1),rowchr,"*703/",substr(s, 4, 4),rowchr,"^2",sep=""); fml; cols<-rep(colIndex+1,rowIndex-1); writeWorksheet(wb, "BMI", sheet = "class", header=F, startRow = 1,startCol=colIndex+1); setCellFormula(wb, "class", rows, cols, fml); saveWorkbook(wb); '); EXPLANATION Get the corners of the data and header area for sheet class class [1,] 1 [2,] 1 [3,] 20 [4,] 5 Get the names of the columns [1] NAME AGE SEX HEIGHT WEIGHT Create the formula for SUM of the Weight column xlconnect has functions to convert row and column numbers to alphanumeric EXCEL cells ie cell(row=2,column=6) = E2. [1] "SUM($E$E:$E$20)" Add header "BMI" Vectorize the formula so we can populate each cell in the BMI column with the appropriate formula [1] "E2*703/D2^2" "E3*703/D3^2" "E4*703/D4^2" "E5*703/D5^2" [5] "E6*703/D6^2" "E7*703/D7^2" "E8*703/D8^2" "E9*703/D9^2" [9] "E10*703/D10^2" "E11*703/D11^2" "E12*703/D12^2" "E13*703/D13^2" [13] "E14*703/D14^2" "E15*703/D15^2" "E16*703/D16^2" "E17*703/D17^2" [17] "E18*703/D18^2" "E19*703/D19^2" "E20*703/D20^2" > library(XLConnect);wb <- loadWorkbook("d:/xls/formulas.xlsx",create = FALSE); box<-getBoundingBox(wb, sheet="class");box;colname <- readWorksheet(wb, sheet="class", start Row = box[1,], startCol = box[1,],endRow = box[1,], endCol = box[4,]);colname; box;colIndex <- which(names(colname) == "WEIGHT");colIndex;rowIndex <- box[3,1];rowIndex;inpu t <- paste(idx2cref(c(box[1,]+1, colIndex, rowIndex, colIndex)), collapse=":"); input;formula <- paste("SUM(", input, ")", sep="");formula;setCellFormula(wb, "class", rowIn dex + 1, colIndex, formula);rows<-c((box[1,]+1):rowIndex); rows;rowchr<-as.character(rows);s<-idx2aref(c((box[1,]+1), colIndex, (box[1,]+1), colIndex-1)); fml<-paste(substr(s, 1, 1),rowchr,"*703/",substr(s, 4, 4),rowchr,"^2",sep="");fml; cols<-rep(colIndex+1,rowIndex-1);writeWorksheet(wb, "BMI", sheet = "class", header=F, startRow = 1,startCol =6);setCellFormula(wb, "class", rows, cols, fml);saveWorkbook(wb); class [1,] 1 [2,] 1 [3,] 20 [4,] 3 [1] NAME HEIGHT WEIGHT <0 rows> (or 0-length row.names) class [1,] 1 [2,] 1 [3,] 20 [4,] 3 [1] 3 class 20 [1] "$C$2:$C$20" [1] "SUM($C$2:$C$20)" [1] 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 [1] "C2*703/B2^2" "C3*703/B3^2" "C4*703/B4^2" "C5*703/B5^2" [5] "C6*703/B6^2" "C7*703/B7^2" "C8*703/B8^2" "C9*703/B9^2" [9] "C10*703/B10^2" "C11*703/B11^2" "C12*703/B12^2" "C13*703/B13^2" [13] "C14*703/B14^2" "C15*703/B15^2" "C16*703/B16^2" "C17*703/B17^2" [17] "C18*703/B18^2" "C19*703/B19^2" "C20*703/B20^2" > ... View more

Correction I don't know what I was thinking but 656 is not large enough, it assumes the string has at least 50 bytes between commas. Oh well, I thinl 16384 is the max. ... View more

Code to create have data have; length cmadlm $500; retain cmadlm '1stwrd'; do str=1 to 10; too=10*uniform(57371); do cma=1 to too; cmadlm=catx(',','aaaa',cmadlm); end; output; cmadlm=' '; end; keep cmadlm; run;quit; ... View more

HAVE Up to 40 obs WORK.HAVE total obs=10 Obs CMADLM 1 aaaa,aaaa,aaaa,aaaa,1stwrd 2 aaaa,aaaa,aaaa,aaaa,aaaa,aaaa,aaaa,aaaa,aaaa 3 aaaa,aaaa,aaaa 4 aaaa,aaaa,aaaa,aaaa,aaaa,aaaa,aaaa,aaaa,aaaa 5 aaaa,aaaa 6 aaaa,aaaa,aaaa,aaaa 7 aaaa,aaaa 8 aaaa,aaaa,aaaa,aaaa,aaaa,aaaa 9 aaaa,aaaa,aaaa,aaaa,aaaa,aaaa,aaaa 10 aaaa,aaaa,aaaa,aaaa,aaaa,aaaa,aaaa,aaaa,aaaa WANT Up to 40 obs WORK.TEMP total obs=10 Obs V1 V2 V3 V4 V5 V6 V7 V8 V9 1 aaaa aaaa aaaa aaaa 1stwrd 2 aaaa aaaa aaaa aaaa aaaa aaaa aaaa aaaa aaaa 3 aaaa aaaa aaaa 4 aaaa aaaa aaaa aaaa aaaa aaaa aaaa aaaa aaaa 5 aaaa aaaa 6 aaaa aaaa aaaa aaaa 7 aaaa aaaa 8 aaaa aaaa aaaa aaaa aaaa aaaa 9 aaaa aaaa aaaa aaaa aaaa aaaa aaaa 10 aaaa aaaa aaaa aaaa aaaa aaaa aaaa aaaa aaaa * The maximum size the array can be is 656 because 656*50=32800; * 50 was mentioned in the instructions - but could set higher 4096; * without performance penaly?; data interim; retain cmacnt 0; set have(in=have); array vars[*] $50 v1-v656; do cma=1 to (countc(cmadlm,',')+1); vars[cma]=scan(cmadlm,cma,','); end; if cmacnt < cma-1 then cmacnt=cma-1; call symputx('cmacnt',put(cmacnt,4.)); run;quit; data temp; set interim(keep=v1-v&cmacnt); run;quit; ... View more