Hi all,
I was wondering, is there a way to use the SAS Excel Plug-in and add in different cells of a sheet different queries that return only one result?
For example, I have one big data set
Product Costs Client
A 1 X
A 2 Y
B 3 X
B 1 X
B 4 Y
And I have a predefined XLS template from our business clients - for example a big sheet where different combinations are specified in different ways:
This XLS is predefined structure format and I was wondering is there is a possibility to write in each cell a different SQL statement to solve the issue, more or less just like using the cubeset formulas when connecting to an OLAP. Then the client could open up the XLS, refress and view the results.
Thanks.
@Reeza: I tried almost all options under all menus and I could not find any which would allow me to filter out some rows and do simple sums. There are options to analyze the data with outputs means, standard deviations and tons of graphs but I could not fine any for running a simple query ...
@rogerjdeangelis: Thanks a lot for the detailed post however this solution becomes more complicated (especially as I don't know R) than the benefits of just pulling data from SAS and filling in the template.
I have decided to move into a new direction with this reporting topic which might be easier for me to implement. Thanks a lot for your great help!
A quick and dirty way is to create the queries on different sheets and link the relevant cells required. You can hide the source sheets if desired.
Thanks for the quick reply.
But how do I add the queries to return only one result? I have seen I can access the data set, I can also filter it but I haven't seen the possibility to run a querry like select sum(costs) where product='A' for example.
Did I miss that button?
What are your options under Tasks>Data?
Even if you can't run a SQL query you should have a summarize data task.
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"
>
@Reeza: I tried almost all options under all menus and I could not find any which would allow me to filter out some rows and do simple sums. There are options to analyze the data with outputs means, standard deviations and tons of graphs but I could not fine any for running a simple query ...
@rogerjdeangelis: Thanks a lot for the detailed post however this solution becomes more complicated (especially as I don't know R) than the benefits of just pulling data from SAS and filling in the template.
I have decided to move into a new direction with this reporting topic which might be easier for me to implement. Thanks a lot for your great help!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.