topic Re: ODS Excel, Left and Top Justify Output in New SAS User

ODS Excel, Left and Top Justify Output

PSU_Sudzi — Thu, 22 Aug 2019 20:48:50 GMT

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;

Re: ODS Excel, Left and Top Justify Output

Reeza — Thu, 22 Aug 2019 22:44:10 GMT

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;

Re: ODS Excel, Left and Top Justify Output

PSU_Sudzi — Thu, 22 Aug 2019 23:48:54 GMT

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?

Re: ODS Excel, Left and Top Justify Output

andreas_lds — Fri, 23 Aug 2019 04:46:05 GMT

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

Re: ODS Excel, Left and Top Justify Output

Reeza — Fri, 23 Aug 2019 15:17:56 GMT

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

Re: ODS Excel, Left and Top Justify Output

Cynthia_sas — Fri, 23 Aug 2019 16:38:46 GMT

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&docsetVersion=9.4&locale=en in the documentation.
Cynthia

Re: ODS Excel, Left and Top Justify Output

PSU_Sudzi — Fri, 23 Aug 2019 17:07:16 GMT

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.

Re: ODS Excel, Left and Top Justify Output

PSU_Sudzi — Fri, 23 Aug 2019 17:08:26 GMT

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

Re: ODS Excel, Left and Top Justify Output

PSU_Sudzi — Mon, 26 Aug 2019 15:54:11 GMT

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

Re: ODS Excel, Left and Top Justify Output

ptimusk — Fri, 05 Jun 2020 10:19:56 GMT

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.