The SAS Output Delivery System and reporting techniques

SAS Excel Plug-in - add queries to cell

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

SAS Excel Plug-in - add queries to cell

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:

  • Sum of all costs
  • Sum per product A where client is X
  • Sum per product B where client is not Y

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.


Accepted Solutions
Solution
‎09-08-2016 01:10 AM
Contributor
Posts: 24

Re: SAS Excel Plug-in - add queries to cell

@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!

View solution in original post


All Replies
Grand Advisor
Posts: 16,285

Re: SAS Excel Plug-in - add queries to cell

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. 

Contributor
Posts: 24

Re: SAS Excel Plug-in - add queries to cell

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?

Grand Advisor
Posts: 16,285

Re: SAS Excel Plug-in - add queries to cell

What are your options under Tasks>Data?

 

Even if you can't run a SQL query you should have a summarize data task. 

Valued Guide
Posts: 505

Re: SAS Excel Plug-in - add queries to cell

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"
>



Solution
‎09-08-2016 01:10 AM
Contributor
Posts: 24

Re: SAS Excel Plug-in - add queries to cell

@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!

Post a Question
Discussion Stats
  • 5 replies
  • 403 views
  • 0 likes
  • 3 in conversation