topic How to create values in designated cell or merging cells in excel before Proc Report listing in ODS and Base Reporting

How to create values in designated cell or merging cells in excel before Proc Report listing

m2l2 — Sat, 14 Oct 2023 02:16:43 GMT

I know how to create the bottom listing under Row 9 by using proc report. But how to create those cells in yellow in one designated cell or designated merging cells as in the above screenshot? Is it doable in proc report title statement?

If not, can I use data _null_ for that? Is it possible to use data _null_ for the top part and Proc Report for the bottom part?

@Cynthia_sas @Ksharp

Re: How to create values in designated cell or merging cells in excel before Proc Report listing

Ksharp — Sat, 14 Oct 2023 09:25:13 GMT

If you don't require too much, could try this simple code:


data have;
array x{8} (8*1);
output;
output;
run;
ods excel file='c:\temp\x.xlsx';
proc report data=have nowd;
columns ('MDCG' 
('MS+NCA'  ('No. of subjects' ('Date of report' (' ' x1))))
(' '  (' ' x2))
(' ' (' ' x3))

('No. of subjects' (' ' (' ' x4))
(' ' (' ' x5)) )

(' ' (' '  x6))

('Reference Member' ('No. of Investion ' (' ' ('Description' x7 x8)))));
define _all_/display;
run;
ods excel close;

Otherwise , you need to resort to RWI skill:

https://communities.sas.com/t5/SAS-Programming/add-dummy-across-columns-header-in-proc-report/m-p/896768

https://communities.sas.com/t5/SAS-Programming/Proc-Report-Merge-Columns/m-p/889989

Re: How to create values in designated cell or merging cells in excel before Proc Report listing

m2l2 — Tue, 17 Oct 2023 00:39:18 GMT

Thanks a lot! I will give it try.

Re: How to create values in designated cell or merging cells in excel before Proc Report listing

Patrick — Tue, 17 Oct 2023 02:33:58 GMT

When it comes to a bit more involved Excel layouts and formatting then one way that often makes things easier to implement and maintain is to keep structure and data separated.

Have your reporting sheet with all the layout and a datasheet. You then create an Excel template with the report sheet that just got the structure and references to the cells in the data sheet. When you run your SAS program you create a copy of the template and create and populate the data sheet (if eventually hide the data sheet).