CALYEAR prac_count freq_range 2011 1181 1--25 2011 616 26--50 2011 411 51--75 2011 591 76--99 2011 64 =100 2011 240 >100 2012 1125 1--25 2012 606 26--50 2012 461 51--75 2012 555 76--99 2012 451 =100 2012 8 >100   After transpose, the column headers become like this.   CALYEAR 1--25 26--50 51--75 76--99 =100 >100 2011 1181 616 411 591 64 240 2012 1125 606 461 555 451 8     Let's put >100 as example, it's char before transpose,  after type in sas, >100 becomes number.  Regardless, the format does not meet sas requirement anyway.   Let's say I want to sum along the row, how should I do? thanks. ... View more

I used proc format to format a variables, want to change the codes (let's say 10 codes) to 3 categories, A B C;   proc format;  value  change    1 = 'A'    2= 'B'   ....; run;     data  data1;      set  data;    format codes  change.; run;   proc sql;     create table example as    select counts(var), sum(var)       from table group by codes; run;     It seems that proc format only cover the codes with the format, because it's still numeric variable and when you group by, you still get 10 groups (corresponding to 10 codes), not three groups.     How to group by the three categories?   thanks.   ... View more

Hi folks, if anyone can provide a comprehensive ODS instruction link, I can figure out myself. thanks. SAS program also attached.   For the questions, the below are the codes;   data rpt100 ; input CALYEAR $ paidserv2 $ counts visits ; datalines;   2016 1 4669 4669 2016 2 2045 4090 2016 3 814 2442 2016 4 364 1456 ; run;   options nodate number orientation=landscape; ods tagsets.ExcelXP file = "&worklib./&reportnm...xls" style = VwaExcel; ods tagsets.ExcelXP options(embedded_titles='yes' embedded_footnotes='yes' background='white' orientation='landscape' FITTOPAGE = 'no' row_repeat = '9-10' autofit_height = 'yes' frozen_headers = 'yes' center_horizontal = 'yes'); ods escapechar='^'; options missing = ''; ods tagsets.ExcelXP options(sheet_name="Test"); title1 justify=left font='Calibri' height=14pt color=white bold bcolor=darkblue "XXXXXXXXXXXXXXXXXXX"; title2 justify=left font='Calibri' italic height=12pt "FFFFFFFFFFFFFFFFFFFFFFFFFFFF"; footnote1 justify=right font='Calibri' height=10pt "SSSSSSSSSSSSSSSS"; footnote2 justify=right font='Calibri' height=10pt "LLLLLLLLLLLLLL"; ods proclabel 'Test'; proc report data=rpt100 nowd split="*"; column calyear paidserv2 counts visits; define calyear / 'Calendar Year' STYLE(column)={TAGATTR='format:text' width=1.5in}; define paidserv2 / 'Frequency ' style(column)={width=2in}; define Counts / 'Counts ' style(column)={width=1.5in}; define Visits / 'Total ' style(column)={width=1.5in}; rbreak after / summarize style={foreground=#000000 background=#C0C0C0 font_weight=bold}; compute after; calyear = 'Total'; endcomp; run;   　 ods tagsets.ExcelXP close;     Please check the result in the excel file.   There are a few things I hope to change: 1, The bluepurple title background need to aligned with the width of table, regardless of the width even if I add more column; 2, There is an extra line under the title; 3, although I defined the footnote as right aligned ( in sas report, it shows me right aligned correctly), but in excel, it shows left aligned. 4, The uncovered background should be grey, which I have no idea how to define this. 5, Lastly, how to come up with the format of specific merged cell like row 19 and 20 in the excel file.     Thank you all for your help, any link or study materials will be very helpful. ... View more