PROC EXPORT to excel range of cells

[JoshS]

Hi All,

I've looked all over and I can't find the answer I'm looking for ( and I'm really hoping it exists!) here.

Basically, I have an excel sheet that I need to export data to, but I need the header column to look a very specific way. So, I've got a template file that I am making a copy of, and then PROC EXPORTing to that copy. Is there anyway for my to specify in my PROC EXPORT statement, that I want the data to start populating the excel sheet starting with row 2?

[RW9]

My suggestion, as always, is if your set on using Excel and a template file, then dump your data to an Excel file or CSV from SAS.  Then write a small VBA macro which loads that and parses it into your Excel template file.  Very simple, only a few lines of VBA macro, but the benefit is you can do what you want with the data.  SAS and other well structured datasets will generally output only well structured data, i.e. column names then data.  Excel on the other hand has virtually no restrictions (which makes it a terrible data medium), i.e. you could put pictures in, have non-rectangular data, etc.

[rogerjdeangelis]

Updating an excel template in place

inspired by
https://goo.gl/RRrEj0
https://communities.sas.com/t5/SAS-Enterprise-Guide/PROC-EXPORT-to-excel-range-of-cells/m-p/329051


HAVE THIS REMPLATE WHITH COLUMN NAMES STARTING IN ROW 2

  +----------------------------------------------------------------+
  |     A      |    B       |     C      |    D       |    E       |
  +----------------------------------------------------------------+
1 |            |            |            |            |            |
  +------------+------------+------------+------------+------------+
2 |  NAME      |   SEX      |    AGE     |  HEIGHT    |  WEIGHT    |
  +------------+------------+------------+------------+------------+
3 |            |            |            |            |            |
  +------------+------------+------------+------------+------------+
   ...
  +------------+------------+------------+------------+------------+
N |            |            |            |            |            |
  +------------+------------+------------+------------+------------+


WANT TO PUPLUATE EXCEL WITH CLASS TABLE IN PLACE (KEEPING FORMATTING)


  +----------------------------------------------------------------+
  |     A      |    B       |     C      |    D       |    E       |
  +----------------------------------------------------------------+
1 |            |            |            |            |            |
  +------------+------------+------------+------------+------------+
2 |  NAME      |   SEX      |    AGE     |  HEIGHT    |  WEIGHT    |
  +------------+------------+------------+------------+------------+
3 | ALFRED     |    M       |    14      |    69      |  112.5     |
  +------------+------------+------------+------------+------------+
   ...
  +------------+------------+------------+------------+------------+
N | WILLIAM    |    M       |    15      |   66.5     |  112       |
  +------------+------------+------------+------------+------------+


SOLUTION

* create template;

%utlfkil(d:/xls/class.xlsx); * delete if exist;
ods excel file="d:/xls/class.xlsx";
ods excel options(sheet_name="class" sheet_interval="none" start_at="A2");
data _null_;
  length name sex age height weight $8;
  retain name sex age height weight ' ';
  file print ods;
  put name sex age height weight;
run;quit;
ods excel close;

* if you have IML you can run this almost verbatim;

* style action is to keep existing style;
%utl_submit_r64('
source("C:/Program Files/R/R-3.3.2/etc/Rprofile.site", echo=T);
library(haven);
library(XLConnect);
have<-read_sas("d:/sd1/class.sas7bdat");
wb <- loadWorkbook("d:/xls/class.xlsx",create = FALSE);
setStyleAction(wb,XLC$"STYLE_ACTION.NONE");
writeWorksheet(wb, have, sheet = "class", header=F, startRow = 3,startCol=1,rownames=NULL);
saveWorkbook(wb);
');

[ballardw]

---

@JoshS wrote:

Hi All,

I've looked all over and I can't find the answer I'm looking for ( and I'm really hoping it exists!) here.

Basically, I have an excel sheet that I need to export data to, but I need the header column to look a very specific way. So, I've got a template file that I am making a copy of, and then PROC EXPORTing to that copy. Is there anyway for my to specify in my PROC EXPORT statement, that I want the data to start populating the excel sheet starting with row 2?

---

SAS Proc EXPORT is designed for data interchange, the values of the columns(variables) and a choice of text for column header, variable name or variable label text, is all that it supports. If you want to control appearance then you might consider one of the report procedures and ODS tagsets.excelxp or Ods Excel to generate the entire output.

[art297]

If you're using EG, the following suggestion will only give you some ideas regarding the VB code that @RW9 mentioned.

http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

It's a macro that was designed to run in base SAS and does exactly what you're trying to accomplish. However, it probably won't run in EG.

Art, CEO, AnalystFinder.com