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;
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;
PROC REPORT will create these types of titles/headings. So use PROC REPORT with ODS EXCEL.
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;
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;
@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.
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.