topic Re: How to write text to specific excel cells, as part of ODS EXCEL? in SAS Programming

How to write text to specific excel cells, as part of ODS EXCEL?

noobnbrnoobest — Wed, 14 Jun 2023 12:03:15 GMT

Hi again!
I need to export my data to an excel file.
So far I get perfectly exported my six column names from my variable names and the actual data.
Now the problem is that the column names need to be on the second row of the sheet (A2, B2, C2, D2, E2, F2),

and they need to have two title texts, for example Maintitle1 at merged cells A1:C1, and Maintitle2 at merged cells D1:F1.

So the desired output would be:

A B C D E F

1 [ M a i n t i t l e 1 ][ M a i n t i t l e 2 ]

2 var1 var2 var3 var4 var5 var6

3 1 6 3 2 7 0

4 0 2 5 8 9 2

5 4 6 0 1 5 8

6 etc.. etc.. etc.. etc.. etc.. etc..

Where the actual data that's exported starts at row three.
Is there a way to achieve this directly from ODS EXCEL?
Thank you so much already in advance!

Edit: The logic should be something like using a TITLE1, but having two title1s with specified excel cells to be merged.
Normally the title1 does a merge spanning across all the columns, in this case it would do a title at merged cells A1:F1.
Somehow just need to divide this to two titles.
Maybe some kind of groups assigned to the variables, the logic would go somewhat like
group1=var1-var3 and group2=var4-var6; title1(group1)=maintitle1; title1(group2)=maintitle2;

Re: How to write text to specific excel cells, as part of ODS EXCEL?

PaigeMiller — Wed, 14 Jun 2023 12:05:31 GMT

PROC REPORT will create these types of titles/headings. So use PROC REPORT with ODS EXCEL.

Re: How to write text to specific excel cells, as part of ODS EXCEL?

Ksharp — Wed, 14 Jun 2023 12:23:18 GMT

ods excel file='c:\temp\temp.xlsx';
proc report data=sashelp.class nowd;
column ('Maintitle1 ' name age sex) ('Maintitle2 ' weight height);
define _all_/display;
run;
ods excel close;

Re: How to write text to specific excel cells, as part of ODS EXCEL?

Tom — Wed, 14 Jun 2023 12:37:04 GMT

That just looks like a normal report to me.

data have ;
  input var1-var6 ;
  put var1-var6;
cards;
1 6 3 2 7 0
0 2 5 8 9 2
4 6 0 1 5 8
;

ods excel file='c:\downloads\headers.xlsx';
proc report data=have;
columns ('header1' var1-var3) ('header2' var4-var6);
define var1-var6 / display;
run;
ods excel close;

Re: How to write text to specific excel cells, as part of ODS EXCEL?

noobnbrnoobest — Wed, 14 Jun 2023 12:47:08 GMT

Thank you so much for these solutions, working perfectly!
Thanks also to @Ksharp and @paigeh

Re: How to write text to specific excel cells, as part of ODS EXCEL?

noobnbrnoobest — Wed, 14 Jun 2023 12:57:59 GMT

The "define"-statement didn't work for me, but I just deleted that row, kept the rest and it seems to work as intended without it.

Re: How to write text to specific excel cells, as part of ODS EXCEL?

Tom — Wed, 14 Jun 2023 13:02:46 GMT

@noobnbrnoobest wrote:
The "define"-statement didn't work for me, but I just deleted that row, kept the rest and it seems to work as intended without it.

The default for numeric variables is to compute the SUM statistic. Since there were no non-numeric variables I had to add the DEFINE statement(s) to tell to just display those numeric variables. In a real report you would use DEFINE statements that match how you want in the report.