topic Re: Exporting a sas data set into .xls in SAS Programming

Exporting a sas data set into .xls

Vladimir — Mon, 19 Dec 2016 19:20:11 GMT

I have in SAS several tables which I’d like to present in Excel from the first row. But I’d like to fix upper left corner. Example below: A1, O1, AC1. Cal I show it in SAS code?

Re: Exporting a sas data set into .xls

ballardw — Mon, 19 Dec 2016 19:31:42 GMT

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

You don't mention what you used to create your output and I'm not opening the xls to try to guess. Anything that involves formatting or appearance options will require an ODS destination. Either ODS Excel or ODS tagsets.excelxp. Report procedures such as Proc Print, Tabulate and Report allow providing options to control output appearance.

Re: Exporting a sas data set into .xls

Reeza — Mon, 19 Dec 2016 20:37:13 GMT

AFAIK you can't get a native Excel file this way, but a different tagset can be used.

See the PANELING option of the the MSOFFICE2K_x Tagsets:

Base SAS: The MSOffice2K_x Tagset Adds Options to the MSOffice2K Tagset

Re: Exporting a sas data set into .xls

rogerjdeangelis — Tue, 20 Dec 2016 16:50:42 GMT

You can do the following with the IML interface to R

HAVE ( Two SAS Datasets  sashelp.class males and females )

Up to 40 obs from sd1.males total obs=10

Obs    NAME       SEX    AGE

  1    Alfred      M      14
  2    Henry       M      14
  3    James       M      12
  4    Jeffrey     M      13
  5    John        M      12
  6    Philip      M      16
  7    Robert      M      12
  8    Ronald      M      15
  9    Thomas      M      11
 10    William     M      15


Up to 40 obs from sd1.females total obs=9

Obs    NAME       SEX    AGE

 1     Alice       F      13
 2     Barbara     F      13
 3     Carol       F      14
 4     Jane        F      12
 5     Janet       F      15
 6     Joyce       F      11
 7     Judy        F      14
 8     Louise      F      12
 9     Mary        F      15

WANT Excel sheet mf with side by side reports

     ------+-----------+------+---------+--------+-----------+------+------+
EXCEL   A  |     B     |   C  |    D    |   E    |   F       |  G   |  H   |
ROW  +-----+-----------+------+---------+--------+-----------+------+------+
  1  |                                                                     |
  2  |         NAME       SEX    AGE                NAME       SEX    AGE  |
  3  |         Alfred      M      14                Alice       F      13  |
  4  |         Henry       M      14                Barbara     F      13  |
  5  |         James       M      12                Carol       F      14  |
  6  |         Jeffrey     M      13                Jane        F      12  |
  7  |         John        M      12                Janet       F      15  |
  8  |         Philip      M      16                Joyce       F      11  |
  9  |         Robert      M      12                Judy        F      14  |
 10  |         Ronald      M      15                Louise      F      12  |
 11  |         Thomas      M      11                Mary        F      15  |
 12  |         William     M      15                                       |
     +-----+-----------+------+---------+--------+-----------+------+------+

 ____        _       _   _
/ ___|  ___ | |_   _| |_(_) ___  _ __
\___ \ / _ \| | | | | __| |/ _ \| '_ \
 ___) | (_) | | |_| | |_| | (_) | | | |
|____/ \___/|_|\__,_|\__|_|\___/|_| |_|

%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 = 7, cellstyle = prcntg);
saveWorkbook(wb,"d:/xls/class1.xlsx");
');
 _     ___   ____
| |   / _ \ / ___|
| |  | | | | |  _
| |__| |_| | |_| |
|_____\___/ \____|

> library(haven);library(XLConnect);males<-read_sas("d:/sd1/males.sas7bdat");
females<-read_sas("d:/sd1/females.sas7bdat");
wb <- loadWorkbook("d:/xls/class_mf.xlsx", create
 = TRUE);createSheet(wb, name = "mf");
writeWorksheet(wb, males, sheet = "mf", startRow = 2, startCol = 2,header=T);
writeWorksheet(wb, females, sheet = "mf", startRow = 2,
startCol = 6,header=T);saveWorkbook(wb);
>
NOTE: 4 lines were written to file PRINT.
Stderr output:
Loading required package: XLConnectJars
XLConnect 0.2-12 by Mirai Solutions GmbH [aut],
  Martin Studer [cre],
  The Apache Software Foundation [ctb, cph] (Apache POI, Apache Commons
    Codec),
  Stephen Colebourne [ctb, cph] (Joda-Time Java library),
  Graph Builder [ctb, cph] (Curvesapi Java library)
http://www.mirai-solutions.com ,
http://miraisolutions.wordpress.com

Re: Exporting a sas data set into .xls

rogerjdeangelis — Tue, 20 Dec 2016 16:52:43 GMT

You can do the following with the IML interface to R

HAVE ( Two SAS Datasets  sashelp.class males and females )

Up to 40 obs from sd1.males total obs=10

Obs    NAME       SEX    AGE

  1    Alfred      M      14
  2    Henry       M      14
  3    James       M      12
  4    Jeffrey     M      13
  5    John        M      12
  6    Philip      M      16
  7    Robert      M      12
  8    Ronald      M      15
  9    Thomas      M      11
 10    William     M      15


Up to 40 obs from sd1.females total obs=9

Obs    NAME       SEX    AGE

 1     Alice       F      13
 2     Barbara     F      13
 3     Carol       F      14
 4     Jane        F      12
 5     Janet       F      15
 6     Joyce       F      11
 7     Judy        F      14
 8     Louise      F      12
 9     Mary        F      15

WANT Excel sheet mf with side by side reports

     ------+-----------+------+---------+--------+-----------+------+------+
EXCEL   A  |     B     |   C  |    D    |   E    |   F       |  G   |  H   |
ROW  +-----+-----------+------+---------+--------+-----------+------+------+
  1  |                                                                     |
  2  |         NAME       SEX    AGE                NAME       SEX    AGE  |
  3  |         Alfred      M      14                Alice       F      13  |
  4  |         Henry       M      14                Barbara     F      13  |
  5  |         James       M      12                Carol       F      14  |
  6  |         Jeffrey     M      13                Jane        F      12  |
  7  |         John        M      12                Janet       F      15  |
  8  |         Philip      M      16                Joyce       F      11  |
  9  |         Robert      M      12                Judy        F      14  |
 10  |         Ronald      M      15                Louise      F      12  |
 11  |         Thomas      M      11                Mary        F      15  |
 12  |         William     M      15                                       |
     +-----+-----------+------+---------+--------+-----------+------+------+

 ____        _       _   _
/ ___|  ___ | |_   _| |_(_) ___  _ __
\___ \ / _ \| | | | | __| |/ _ \| '_ \
 ___) | (_) | | |_| | |_| | (_) | | | |
|____/ \___/|_|\__,_|\__|_|\___/|_| |_|

%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 = 7, cellstyle = prcntg);
saveWorkbook(wb,"d:/xls/class1.xlsx");
');
 _     ___   ____
| |   / _ \ / ___|
| |  | | | | |  _
| |__| |_| | |_| |
|_____\___/ \____|

> library(haven);library(XLConnect);males<-read_sas("d:/sd1/males.sas7bdat");
females<-read_sas("d:/sd1/females.sas7bdat");
wb <- loadWorkbook("d:/xls/class_mf.xlsx", create
 = TRUE);createSheet(wb, name = "mf");
writeWorksheet(wb, males, sheet = "mf", startRow = 2, startCol = 2,header=T);
writeWorksheet(wb, females, sheet = "mf", startRow = 2,
startCol = 6,header=T);saveWorkbook(wb);
>
NOTE: 4 lines were written to file PRINT.
Stderr output:
Loading required package: XLConnectJars
XLConnect 0.2-12 by Mirai Solutions GmbH [aut],
  Martin Studer [cre],
  The Apache Software Foundation [ctb, cph] (Apache POI, Apache Commons
    Codec),
  Stephen Colebourne [ctb, cph] (Joda-Time Java library),
  Graph Builder [ctb, cph] (Curvesapi Java library)
http://www.mirai-solutions.com ,
http://miraisolutions.wordpress.com

Re: Exporting a sas data set into .xls

rogerjdeangelis — Tue, 20 Dec 2016 16:53:32 GMT

I posted the wrong solution code

Here is the correct code

Re: Exporting a sas data set into .xls

rogerjdeangelis — Tue, 20 Dec 2016 16:53:48 GMT

libname sd1 "d:/sd1";
data sd1.males sd1.females;
set sashelp.class(keep=name sex age);
if sex='M' then output sd1.males;
else output sd1.females;
run;quit;

%utlfkil(d:/xls/class_mf.xlsx);
%utl_submit_r64('
library(haven);
library(XLConnect);
males<-read_sas("d:/sd1/males.sas7bdat");
females<-read_sas("d:/sd1/females.sas7bdat");
wb <- loadWorkbook("d:/xls/class_mf.xlsx", create = TRUE);
createSheet(wb, name = "mf");
writeWorksheet(wb, males, sheet = "mf", startRow = 2, startCol = 2,header=T);
writeWorksheet(wb, females, sheet = "mf", startRow = 2, startCol = 6,header=T);
saveWorkbook(wb);
');

Re: Exporting a sas data set into .xls

Vladimir — Wed, 21 Dec 2016 16:17:19 GMT

| | / _ \ / ___|

| | | | | | | _

| |__| |_| | |_| |

|_____\___/ \____|

WARNING: Apparent invocation of macro UTL_SUBMIT_R64 not resolved.
486 %utl_submit_r64('
-
180
NOTE: The quoted string currently being processed has become more than 262 characters long. You
might have unbalanced quotation marks.

ERROR 180-322: Statement is not valid or it is used out of proper order.