SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasuser_sk
Quartz | Level 8

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;

 

 

sasuser_sk_0-1628014012326.png

 

1 ACCEPTED SOLUTION
2 REPLIES 2

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 952 views
  • 1 like
  • 2 in conversation