Consider a custom format instead.   data Temp1; input Round $ Var1 Var2 Var3 Var4; datalines; Var1 0 1000 2000 3000 Var2 1111 0 2222 3333 Var3 1234 2345 0 3456 Var4 1122 2211 3322 0 ; run; proc format; value zero_fmt 0 = '--' other = [comma10.0]; run; proc print data=temp1; format _numeric_ zero_fmt.; run; Results:   Obs Round Var1 Var2 Var3 Var4 1 Var1 -- 1,000 2,000 3,000 2 Var2 1,111 -- 2,222 3,333 3 Var3 1,234 2,345 -- 3,456 4 Var4 1,122 2,211 3,322 -- @mcook wrote: in proc report, or with ODS.  Given a Column with numeric format,  is it possible to change a few specified cells to character, and input a character string? specifically,  replace all 0's or . to '--'? Options Missing='--' does not work as it will only accept a single character. I have been able to have 2 or more numeric formats in a column, but am unable to convert some cells to character. I have tried the following. (also tried ODS escapechar with no luck)   data Temp1; input Round $ Var1 Var2 Var3 Var4; datalines; Var1 0 1000 2000 3000 Var2 1111 0 2222 3333 Var3 1234 2345 0 3456 Var4 1122 2211 3322 0 ; run; ODS RTF File="&FilePath.\Testing.rtf"; ODS ESCAPECHAR='^'; proc report data=temp1; columns Round var1 var2 var3 var4; define Round / display; define Var1 / analysis; define Var2 / analysis; define Var3 / analysis; define Var4 / analysis; compute Var1; if Var1=0 then do; call define(_Col_,'format','$10.'); Var1='--'; end; if Var1 ^=0 then do; call define (_Col_,'format','Comma10.0'); end; endcomp; run; ODS RTF CLose;       ... View more

Or this one:   data ClassTable; set SASHELP.Class; Section="A"; run; data ClassTable1; set SASHelp.Class; Section="B"; run; data ClassTable; set ClassTable ClassTable1; run; proc sql noprint undo_policy=none; delete from ClassTable where Name like '%J%' and Section="B"; quit; proc sort data=ClassTable; by Sex Name Section; run; data ClassTable; set ClassTable; by sex name; if last.Name=0 then ID=1; if (last.Name=1) AND (First.Name=1) then ID=1; if (Last.Name=0) AND (First.Name=0) then ID=0; run; ods RTF file="c:\temp\RowRemove2.RTF" bodytitle; ods escapechar='^'; options nonumber nodate; proc report data=classtable spanrows out=x style(report)=[rules=cols frame=hsides] style(Header)=[borderleftwidth=0.1pt borderleftcolor=black borderrightwidth=0.1pt borderrightcolor=black borderbottomwidth=0.1pt borderbottomcolor=black] style(Column)=[VJust=C]; columns ID sex Name Weight; define ID / display; define sex / group center style(Column)=[borderbottomwidth=0.1pt borderbottomcolor=black bordertopwidth=0.1pt bordertopcolor=black]; define name / group center format=$25. style(Column)=[borderbottomwidth=0.1pt borderbottomcolor=black bordertopwidth=0.1pt bordertopcolor=black cellwidth=1.5in]; define Weight / analysis center; break after sex / summarize suppress style=[font_style=roman font_Weight=bold]; compute after sex; Name='Total'; call define('sex','style','style=[borderbottomwidth=2 borderbottomcolor=black bordertopwidth=2 bordertopcolor=white]'); call define('weight.sum','style','style=[bordertopwidth=0.1pt bordertopcolor=black]'); endcomp; Compute Weight; call define(_Col_,'style','style=[fontweight=light]'); if ID=1 then call define(_col_,'style','style=[bordertopwidth=0.1pt bordertopcolor=black]'); /* call define('sex','style','style=[borderbottomwidth=0.1pt borderbottomcolor=white */ /* bordertopwidth=0.1pt bordertopcolor=white]');*/ /* if _break_='Sex' then*/ /* call define('sex','style','style=[borderbottomwidth=2px borderbottomcolor=black*/ /*bordertopwidth=0.1pt bordertopcolor=white]');*/ EndComp; run; ods rtf close; ... View more

Hi @Ksharp ,  It's a very clever way to handle this request, and your program works nicely for a small dataset, in one page.  The program would not work for a large dataset, ODS tagsets.rtf works better for a large dataset, see the code: proc sort data=sashelp.zipcode out=zipcode(keep=timezone statename countynm city zip); by timezone statename countynm city zip; run; data zipcode; set zipcode; by timezone statename countynm city zip; *if _n_ le 400; if last.city then id=0; else id=1; run; ods tagsets.RTF file="~/sasout/RowRemove.RTF"; options nonumber nodate; proc report data=zipcode spanrows style(header)=[borderbottomcolor=black borderbottomwidth=2px ] style(report)=[ rules=cols] style(Column)=[VJust=C]; columns ID timezone statename countynm city zip; define timezone / group center; define statename / group center; define countynm / group center; define city / group center; define zip / display Center; define ID / display noprint; compute city; if id=0 then call define (_row_,'style','style=[borderbottomcolor=black borderbottomwidth=2px ]'); call define ('Timezone','style','style=[borderbottomcolor=black borderbottomwidth=2px ]'); call define ('statename','style','style=[borderbottomcolor=black borderbottomwidth=2px ]'); call define ('countynm','style','style=[borderbottomcolor=black borderbottomwidth=2px ]'); call define ('city','style','style=[borderbottomcolor=black borderbottomwidth=2px ]'); endcomp; run; ods tagsets.rtf close; but it creates a rtf file that is not a Word document.  That is, if you select a few rows in the column of City, and change the font size to a large number, say 24 points, then you can't read the city name very well, unless you also adjust the cell height, then that will break your table, in terms of pagination.  This is because ODS tagsets.rtf uses an absolute cell height to control the vertical spaces to achieve correct pagination.    By the way, if you run the whole zipcode data, you will have an output of 1100+ pages, there must be a better way to create a zipcode book for about 100 pages.  Think of a dictionary and an alternative for SPANROWS.    Many Thanks Jianmin Long       ... View more

"why the empty space before 'Total'?" I want make "Total" row in the first place . sas would order it by character . since blank is before any of A-Z in ASIIC code, so "Total" would be in the first row. "Also, why no column statement?" Yes. you could define COLUMN statement to order variables by Column Groups SubGroup nCount Var3 Var2 Var5 Var4 Var1; I didn't write it due to save the code. "but it drops the Groups column." Yes. Because I used NOPRINT option. define groups/group noprint; ... View more

How about this one ?   ods Tagsets.RTF file='c:\temp\temp.rtf' style=journal; proc sql; create table have as select sex,1 as id,Smoking_Status,sum(weight) as weight from sashelp.heart group by sex,Smoking_Status union select sex,2 as id,'Total',sum(weight) as weight from sashelp.heart group by sex ; quit; proc report data=have nowd spanrows missing style(report)={rules=cols } style(header)={fontstyle=roman fontweight=bold background=verylightgrey borderbottomcolor=black borderbottomwidth=2px} ; columns sex id Smoking_Status weight; define sex/group style={just=c vjust=m bordertopcolor=black bordertopwidth=2px}; define id/group noprint; define Smoking_Status/group; define weight/display; compute Smoking_Status; if Smoking_Status='Total' then call define(_row_,'style','style={bordertopcolor=black bordertopwidth=2px borderbottomcolor=black borderbottomwidth=2px}'); endcomp; run; ods Tagsets.RTF close; ... View more

Hi @Cynthia_sas and @mcook ,    It looks like @Cynthia_sas has a solution for @mcook 's request, but a simple solution for this one would be something like a change in your code:  pretext='\ql Table1.1 Temp Title\line' The control word \ql will put the title line on the left, while \qr will do the right alignment.  You guess it, \qc will do the center alignment, but that would be default for PRETEXT.  I also took the liberty of adding a line of space between the title line and the table, which is achieved by  the control word \line.  You can have more space by adding more \line, or none by deleting it completely.    Many Thanks Jianmin Long ... View more

In my experience ODS is resource intensive so I wouldn't use it for large spreadsheets because it likely to be slower. PROC EXPORT or LIBNAME with the XLSX engine are preferable options.  ... View more

One more tiny pebble to add the the cairn of explanation you got so far. %sysfunc must decide how to parse the code: is it macro code or is it text (that could be be passed to the sas language parser)? For EQ, it decides it's macro code. I couldn't find a way to force the macro parser to see this as a simple string in this case.      ... View more

Any quick tips on how to create a copy of a file, and then run this code on the copy? are proc copy, and fcopy the only options? ... View more

@mcook wrote: Yes I am using proc import after removing the carriage returns. I understand that removing or replacing characters would change the length, and format upon import. but the change in the Date, and uppercase formats, have me stumped. There should have been no carriage returns to be replaced in either of those columns, So i would assume they would import identically with both tables. Really would need to see the files, before and after, as well as the Proc Import code and anything else done to the data sets before running Proc Compare. ... View more