DATA Step, Macro, Functions and more

Exporting a sas data set into .xls

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Exporting a sas data set into .xls

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?


Accepted Solutions
Solution
‎12-20-2016 08:16 AM
Super User
Posts: 17,835

Re: Exporting a sas data set into .xls

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

View solution in original post


All Replies
Super User
Posts: 10,500

Re: Exporting a sas data set into .xls

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.

Solution
‎12-20-2016 08:16 AM
Super User
Posts: 17,835

Re: Exporting a sas data set into .xls

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

Valued Guide
Posts: 505

Re: Exporting a sas data set into .xls

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

Valued Guide
Posts: 505

Re: Exporting a sas data set into .xls

 

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

 

Valued Guide
Posts: 505

Re: Exporting a sas data set into .xls

I posted the wrong solution code

Here is the correct code
Valued Guide
Posts: 505

Re: Exporting a sas data set into .xls

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);
');
New Contributor
Posts: 3

Re: Exporting a sas data set into .xls

| | / _ \ / ___|

| | | | | | | _

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

|_____\___/ \____|

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.
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 255 views
  • 0 likes
  • 4 in conversation