Data in Column name separated into two lines

sasuser_sk — Tue, 03 Aug 2021 18:07:32 GMT

Hello Everyone - I have this code below which works just fine. Output is exported to excel. Problem is it splits data for field MDL_NM in 2 lines. Even after expanding the column it stays in 2 lines. Can someone please help fix my code. Has frozen_headers something to do with this problem? Output is shown as pic.

options nobyline;
ods excel options(sheet_interval='table' sheet_name='Sales' suppress_bylines='on' frozen_headers="6" );

TITLE1 justify= left color= black height=8pt "abc";
Title2 justify= left color= black height=8pt "xyz";
Title3 justify= left color= black height=8pt "zzz";
Title4 justify= left color= black height=8pt "yyy";
Title5 justify= left color= black height=8pt "aaa";
FOOTNOTE1 "Generated by the SAS System (&_SASSERVERNAME, &SYSSCPL) on %TRIM(%QSYSFUNC(DATE(), NLDATE20.)) at %TRIM(%SYSFUNC(TIME(), TIMEAMPM12.))";

proc report data=WORK.QUERY_FOR_TRNSTRANSPOSED_000B nowd;
column Net MFR_NM MDL_NM '2 Year'n, SUM='2 Year_SUM'n '3 Year'n, SUM='3 Year_SUM'n Monthly, SUM=Monthly_SUM Weekly, SUM=Weekly_SUM Total1, SUM=Total1_SUM 'New Prod'n, SUM='New Prod_SUM'n 'Upgrade_Prod'n, SUM='Upgrade__Prod_SUM'n Upgrade, SUM=Upgrade_SUM Total, SUM=Total_SUM;
define Net / display 'Net' missing;
compute Net;
if _break_ eq ' ' then do;
if Net ne ' ' then hold1=Net;
end;
if upcase(_break_)="NET" then do;
call define("Net", 'style', 'style=[pretext="Subtotal "]');
end;
if _break_='_RBREAK_' then do;
call define("Net", 'style', 'style=[pretext="Total"]');
end;
endcomp;
define MFR_NM / display 'MFR_NM' format=$50. missing order=formatted;
compute MFR_NM;
if _break_ eq ' ' then do;
if MFR_NM ne ' ' then hold2=MFR_NM;
end;
endcomp;
define MDL_NM /width=100 display 'MDL_NM' format=$50. missing order=formatted;
compute MDL_NM;
if _break_ eq ' ' then do;
if MDL_NM ne ' ' then hold3=MDL_NM;
end;
endcomp;
define '2 Year'n / analysis SUM '2 Year' missing;
define '3 Year'n / analysis SUM '3 Year' missing;
define Monthly / analysis SUM 'Monthly' missing;
define Weekly / analysis SUM 'Weekly' missing;
define Total1 / analysis SUM 'Total1' missing;
define 'New Prod'n / analysis SUM 'New Prod' missing;
define 'Upgrade_Prod'n / analysis SUM 'Upgrade_Prod' missing;
define Upgrade / analysis SUM 'Upgrade' missing;
define Total / analysis SUM 'Total' missing;
rbreak after / summarize;
run;
quit;
TITLE; FOOTNOTE;

ods excel close;

Re: Data in Column name separated into two lines

Tom — Tue, 03 Aug 2021 18:26:38 GMT

Try using FLOW='tables' option of ODS EXCEL.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/odsug/p09n5pw9ol0897n1qe04zeur27rv.htm

Re: Data in Column name separated into two lines

sasuser_sk — Tue, 03 Aug 2021 18:38:35 GMT

Thank you Tom! Your suggestion helped.

topic Data in Column name separated into two lines in SAS Programming

Data in Column name separated into two lines

Re: Data in Column name separated into two lines

Re: Data in Column name separated into two lines