BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Vladimir
Fluorite | Level 6

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

7 REPLIES 7
ballardw
Super User

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.

Reeza
Super User

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

rogerjdeangelis
Barite | Level 11
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

rogerjdeangelis
Barite | Level 11

 

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

 

rogerjdeangelis
Barite | Level 11
I posted the wrong solution code

Here is the correct code
rogerjdeangelis
Barite | Level 11
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);
');
Vladimir
Fluorite | Level 6
| | / _ \ / ___|

| | | | | | | _

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

|_____\___/ \____|

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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