ODS Excel, Left and Top Justify Output

[PSU_Sudzi]

I am trying to left and top justify the Excel output but am not sure what options/code would do it. Any suggestions on what to add to this code? Bonus points if there is a way to suppress the output/results window and just dump this all right to Excel.

ods excel file= "path to file here.xlsx"
options(
sheet_interval="none"
sheet_name="&sysdate."
frozen_headers="1"
autofilter='on'
);

proc report data=sample_dataset;
title "Your Title Here";

run;

ods excel close;

[Reeza]

No idea how to get the top alignment portion. You may need to use STYLE statements within PROC REPORT.

Did you want the title in the header or in the worksheet itself?

*closes all other output destinations - you will need to reopen them;
ods _all_ close;

*opens excel output;
ods excel file= "/home/fkhurshed/Demo1/delete1.xlsx"
options(
sheet_interval="none"
sheet_name="&sysdate."
frozen_headers="1"
autofilter='on'
embedded_titles='yes'
);

proc report data=sashelp.class;
title j=l "Your Title Here";

run;

ods excel close;


*opens destination again - choose the ones you need;
ods html;
ods listing;

---

@PSU_Sudzi wrote:

I am trying to left and top justify the Excel output but am not sure what options/code would do it. Any suggestions on what to add to this code? Bonus points if there is a way to suppress the output/results window and just dump this all right to Excel.

ods excel file= "path to file here.xlsx"
options(
sheet_interval="none"
sheet_name="&sysdate."
frozen_headers="1"
autofilter='on'
);

proc report data=sample_dataset;
title "Your Title Here";

run;

ods excel close;

---

[PSU_Sudzi]

Thanks Reeza! If I could get the title to print across the top row of the Excel that would be cool.

Does the 'title j=l' only align the title left or the whole excel file, column headers and values, that's what I'm looking to do?

[andreas_lds · Accepted solution]

---

@PSU_Sudzi wrote:

Thanks Reeza! If I could get the title to print across the top row of the Excel that would be cool.

Does the 'title j=l' only align the title left or the whole excel file, column headers and values, that's what I'm looking to do?

---

Only the title is aligned left. Adding EMBEDDED_TITLES="yes" to your ods-options should place the title where you want it.

I am trying to left and top justify the Excel output

Not sure that i understand what you need. Should all rows be left/top justified? Try

proc report data=sashelp.class style(column header)=[textalign=left verticalalign=top];
title "Your Title Here";
run;

[PSU_Sudzi]

Thank you Andreas_lds. What I am looking for is to have all of the values/text in the cells align to the left and top of the cell, as well as the headers. I think by default when you send something to Excel it seems to align the cell text/values to the right and bottom. Basically I'm trying to use ODS to customize my output into Excel so I no longer need to open the file and apply filters, alignments, etc. by hand. I will try your suggestion also when I am back in the office on Monday.

[Reeza]

---

@PSU_Sudzi wrote:

Thanks Reeza! If I could get the title to print across the top row of the Excel that would be cool.

Does the 'title j=l' only align the title left or the whole excel file, column headers and values, that's what I'm looking to do?

---

You'll need to do that via styles in PROC REPORT then, or you can try just specifying the justification there as well. 

[Cynthia_sas]

Hi:
You may not need a style template change. There is also a TITLE_FOOTNOTE_WIDTH suboption that controls the number of columns that titles and footnotes should span. You can find out about that suboption and the other valid suboptions here: https://go.documentation.sas.com/?docsetId=odsug&docsetTarget=p09n5pw9ol0897n1qe04zeur27rv.htm&docse... in the documentation.
Cynthia

[PSU_Sudzi]

Thank you Cynthia, I will review this when I am back in the office on Monday.

[PSU_Sudzi]

These have all been great tips and I am able to get the output I'm looking for, thank you all!!!

[ptimusk]

That justify left, j=l also works nicely with both titles and footnotes with PROC PRINT inside an ODS Excel in SAS 9.4 wrapper.