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?
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
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.
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
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
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
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.