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

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

Tom_0-1686746215995.png

 

View solution in original post

6 REPLIES 6
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Ksharp
Super User
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;

Ksharp_0-1686745393904.png

 

Tom
Super User Tom
Super User

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;

Tom_0-1686746215995.png

 

noobnbrnoobest
Fluorite | Level 6

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

noobnbrnoobest
Fluorite | Level 6
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.
Tom
Super User Tom
Super User

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

SAS Innovate 2025: Call for Content

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!

Submit your idea!

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
  • 6 replies
  • 982 views
  • 3 likes
  • 4 in conversation