@Kathryn_SAS ,  Awesome!  This solved my problem, thank you so much! ... View more

@ballardw , proc format might change data format in other destinations like html, but it seems to have no influence over .xml outputs. Recently, @IyenJ  helped me with formatting .xml cells using "Style(column)={TAGATTR='format:0.00'}" (https://communities.sas.com/t5/SAS-Programming/Proc-Report-to-ODS-EXCEL-xml-issue-in-writing-last-0-after-the/td-p/859800).  Based on your suggestion and above experience I've tried to use {tagattr= "format: $labelindent."}. This creates output in Results window without format, but not creating any .xml output.  ods tagsets.excelxp file = "C:\Users\Desktop\test\Score_tables.xml" style=listing options(sheet_name= "Table 3 - Categorical" embedded_titles = "on" embedded_footnotes = "on" Merge_Titles_Footnotes = "on" missing_align= 'center' Row_Heights='24,16,0,32,32,0,0' Skip_Space= '0,0,0,1,1'); proc report data= have style(report)={background=white borderrightcolor=white borderleftcolor=white bordertopcolor=black borderbottomcolor=black bordertopwidth=1pt borderbottomwidth=1pt} style(header)={height=24pt font_face='TREBUCHET MS' fontsize=11pt background=white borderrightcolor=white borderleftcolor=white bordertopcolor=black borderbottomcolor=black bordertopwidth=1 borderbottomwidth=1} out=report_scorechange; column label N cat1 cat2 cat3; define Label/ "" width=25 style(column)= {just=l font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt TAGATTR='format: $labelindent.'} display; define N/ "Total" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display; define cat1/ "Excellent" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display; define cat2/ "Good" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display; define cat3/ "Fair" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display; run; ods tagsets.excelxp close; ... View more

@ballardw,  Thank you for your feedback.  Below if my code from Proc report and Data (simulated) that i'm working on. I was using  define VISIT/ group statement. But order=data option is what I'm looking for. It really does the job the way I expected. Thank you! @Kurt_Bremser, I like the approach you suggested, but the above way seems more efficient in my situation. I appreciate your time and support! @Shaik_Adil, I'm providing the data and codes below as an answer to your question. Thank you for checking in with me! Again, thank you all for spending time to read and answer my post! data have; length Visit $25; input VISIT $ _n _mean _std ; datalines; Week12 210 0.55 0.84 Month8 150 0.05 0.73 Month12 120 0.07 0.51 Month16 135 0.33 0.65 Month20 101 0.06 0.43 Month24 256 0.35 0.71 Month28 175 0.11 0.43 ; run; ods tagsets.excelxp file = "C:\Users\Desktop\test\Score_tables.xml" style=listing options(sheet_name= "Table 3 - Categorical" embedded_titles = "on" embedded_footnotes = "on" Merge_Titles_Footnotes = "on" missing_align= 'center' Row_Heights='24,16,0,32,32,0,0' Skip_Space= '0,0,0,1,1'); proc report data= have spanrows style(report)={background=white borderrightcolor=white borderleftcolor=white bordertopcolor=black borderbottomcolor=black bordertopwidth=1pt borderbottomwidth=1pt} style(header)={height=24pt font_face='TREBUCHET MS' fontsize=11pt background=white borderrightcolor=white borderleftcolor=white bordertopcolor=black borderbottomcolor=black bordertopwidth=1 borderbottomwidth=1} out=report_scorechange; column visit _n _mean _std; define visit/ "VISIT" width=25 style(header)= {just=l} style(column)= {just=l font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} group; define _n/ "n" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display; define _mean/ "Mean" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display; define _std/ "SD" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display; compute before visit/ style = {indent=2 just=l font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt}; Line @3 Text $50.; length text $50; if strip(visit) eq "Week12" then text= "Reference Timepoint"; else text= "Change from Week 12"; endcomp; run; ods tagsets.excelxp close;   ... View more

@PaigeMiller ,  Thank you for your input! I should have specified that was tagsets.excelxp destination, not excel itself. Unsure about how much different are options for those two destinations, I applied format 4.2 to the column in define statement(define Mean/ "Mean" center width=8 format=4.2 display;) , which rounded up the last decimal in xml file.. ods tagsets.excelxp options (sheet_name= "Table 7." start_at= "B3" embedded_titles = "on" embedded_footnotes = "on" Merge_Titles_Footnotes = "on" Row_Heights='0,0,0,32,32,0,0' Skip_Space= '0,0,0,0,1'); proc report data= items_final style(report)={background=white borderbottomcolor=black borderbottomwidth=1} style(header)={background=white borderrightcolor=white borderleftcolor=white bordertopcolor=black borderbottomcolor=black bordertopwidth=1 borderbottomwidth=1}; column ITEM LABEL N MEAN SD cat0 cat1 cat2 cat3 cat4 cat5 Missing; define ITEM/ "Item ID" left width=10 display; define LABEL/ "Item Content" left width=30 display; define n/ "Valid n" center width=8 display; define Mean/ "Mean" center width=8 Style(column)={TAGATTR='format:0.00'} display; define SD/ "SD" center width=8 Style(column)={TAGATTR='format:0.00'} display; define cat0/ "0" center width=11 display; define cat1/ "1" center width=11 display; define cat2/ "2" center width=11 display; define cat3/ "3" center width=11 display; define cat4/ "4" center width=11 display; define cat5/ "5" center width=11 display; define Missing/ "Missing" center width=11 display; run; title;footnote; ... View more

@IyenJ ,  Actually it was ODS tagsets.excelxp destination. And column(style) with tagattr=format works perfectly there. Thank you so much! ... View more

Awesome! This really works.  Thank you so much, @Tom  and @Kurt_Bremser ! ... View more

Thank you @Tom  and  @Kurt_Bremser ! I appreciate your support, this way of reading data in SAS is something fundamental that I should learn.  @Tom , regarding the second part of the code, each file (31 files) is located in a separate folder with a different names. Only common part is .txt extension for all files(different file names as well). Therefore, I've hardcoded the list of files in earlier steps into &file1-&file31.. This part is something I could handle by myself, but thank you so much for your input!   ... View more

Thank you, @Kurt_Bremser ! This is giving the desired output, but with multiple _ERROR_ in the log, due to different data type. Starting from row 27 SE variable has only -- (double dash) which is causing the error. Could this error be avoided by using any INFILE statement options? P(#) Est SE Grad 1 -0.91 0.04 0.00 2 -1.01 0.04 0.00 3 -0.96 0.04 0.00 . . 25 0.98 0.85 0.00 26 -1.30 1.32 0.00 27 0.00 -- 28 0.00 -- 29 0.00 -- 30 0.00 -- . . 89 0.00 -- 90 0.00 -- 91 1.00 -- -2*log(LL) = 85741.39 #Cycles A-time E-time D-time M-time S-time Total 535 0.00 2.69 0.00 0.48 0.61 3.78 Below is the SAS code and it's log.  %macro import; %do i= 1 %to 31; filename dbg_new "&newfolder.\&&file&i...txt"; data new_%scan(&&file&i, -1, -)&i; infile dbg_new firstobs=2 truncover; input @; if index(_infile_,"=") then stop; input p_ est se grad; run; %end; %mend; %import;   NOTE: Invalid data for se in line 28 29-30. RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+-- 28 27 0.00 -- 30 p_=27 est=0 se=. grad=. _ERROR_=1 _INFILE_=27 0.00 -- _N_=27 NOTE: Invalid data for se in line 29 29-30. 29 28 0.00 -- 30 p_=28 est=0 se=. grad=. _ERROR_=1 _INFILE_=28 0.00 -- _N_=28 NOTE: Invalid data for se in line 30 29-30. 30 29 0.00 -- 30 p_=29 est=0 se=. grad=. _ERROR_=1 _INFILE_=29 0.00 -- _N_=29 NOTE: Invalid data for se in line 31 29-30. 31 30 0.00 -- 30 p_=30 est=0 se=. grad=. _ERROR_=1 _INFILE_=30 0.00 -- _N_=30 NOTE: Invalid data for se in line 32 29-30. 32 31 0.00 -- 30 p_=31 est=0 se=. grad=. _ERROR_=1 _INFILE_=31 0.00 -- _N_=31 NOTE: Invalid data for se in line 33 29-30. 33 32 0.00 -- 30 p_=32 est=0 se=. grad=. _ERROR_=1 _INFILE_=32 0.00 -- _N_=32 NOTE: Invalid data for se in line 34 29-30. 34 33 0.00 -- 30 p_=33 est=0 se=. grad=. _ERROR_=1 _INFILE_=33 0.00 -- _N_=33 NOTE: Invalid data for se in line 35 29-30. 35 34 0.00 -- 30 p_=34 est=0 se=. grad=. _ERROR_=1 _INFILE_=34 0.00 -- _N_=34 NOTE: Invalid data for se in line 36 29-30. 36 35 0.00 -- 30 p_=35 est=0 se=. grad=. _ERROR_=1 _INFILE_=35 0.00 -- _N_=35 NOTE: Invalid data for se in line 37 29-30. 37 36 0.00 -- 30 p_=36 est=0 se=. grad=. _ERROR_=1 _INFILE_=36 0.00 -- _N_=36 NOTE: Invalid data for se in line 38 29-30. 38 37 0.00 -- 30 p_=37 est=0 se=. grad=. _ERROR_=1 _INFILE_=37 0.00 -- _N_=37 NOTE: Invalid data for se in line 39 29-30. 39 38 0.00 -- 30 p_=38 est=0 se=. grad=. _ERROR_=1 _INFILE_=38 0.00 -- _N_=38 NOTE: Invalid data for se in line 40 29-30. 40 39 0.00 -- 30 p_=39 est=0 se=. grad=. _ERROR_=1 _INFILE_=39 0.00 -- _N_=39 NOTE: Invalid data for se in line 41 29-30. 41 40 0.00 -- 30 p_=40 est=0 se=. grad=. _ERROR_=1 _INFILE_=40 0.00 -- _N_=40 NOTE: Invalid data for se in line 42 29-30. 42 41 0.00 -- 30 p_=41 est=0 se=. grad=. _ERROR_=1 _INFILE_=41 0.00 -- _N_=41 NOTE: Invalid data for se in line 43 29-30. 43 42 0.00 -- 30 p_=42 est=0 se=. grad=. _ERROR_=1 _INFILE_=42 0.00 -- _N_=42 NOTE: Invalid data for se in line 44 29-30. 44 43 0.00 -- 30 p_=43 est=0 se=. grad=. _ERROR_=1 _INFILE_=43 0.00 -- _N_=43 NOTE: Invalid data for se in line 45 29-30. RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+-- 45 44 0.00 -- 30 p_=44 est=0 se=. grad=. _ERROR_=1 _INFILE_=44 0.00 -- _N_=44 NOTE: Invalid data for se in line 46 29-30. 46 45 0.00 -- 30 p_=45 est=0 se=. grad=. _ERROR_=1 _INFILE_=45 0.00 -- _N_=45 NOTE: Invalid data for se in line 47 29-30. WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed. 47 46 0.00 -- 30 p_=46 est=0 se=. grad=. _ERROR_=1 _INFILE_=46 0.00 -- _N_=46 NOTE: 92 records were read from the infile DBG_NEW. The minimum record length was 21. The maximum record length was 42. NOTE: The data set WORK.NEW_DBG31 has 91 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds ..  ... View more

Hi @Tom , Thank you for your answer, it is very helpful! As I never used infile statement to read data before, don't know enough about it's power. What would be the code for reading the ready file (C:\Users\Files\dbg.text) in your example?  The number of observations vary per file, but data sample won't (eg. about first  25-50 obs are numeric values ordered in 4 columns, followed by obs containing unorganized lines of texts). It would be ideal to know also how to stop reading data once numeric values ends.    ... View more