BookmarkSubscribeRSS Feed
Discaboota
Obsidian | Level 7
  TEAM 1 TEAM 2
  N+R Resolved TOTAL N+R Resolved TOTAL
CYCLE PRODUCT Count POS POS% Count POS POS% Count POS Count POS POS% Count POS POS% Count POS
xx a 1 0.11 3.18% 7 0.50 14.91% 46 3.37 0 0 0.00% 2 0.10 16.32% 11 0.62
  TOTAL 1 0.11 3.18% 7 0.50 14.91% 46 3.37 0 0 0.00% 2 0.10 16.32% 11 0.62
yy a 0 0 0.00% 0 0 0.00% 3 0.48 0 0 0.00% 0 0 0.00% 2 0.38
  TOTAL 0 0 0.00% 0 0 0.00% 3 0.48 0 0 0.00% 0 0 0.00% 2 0.38
zz a 74 7.56 6.74% 289 32.58 29.06% 1059 112.10 21 2.86 9.21% 62 9.45 30.44% 244 31.05
  TOTAL 74 7.56 6.74% 289 32.58 29.06% 1059 112.10 21 2.86 9.21% 62 9.45 30.44% 244 31.05
aa a 1 0.03 0.71% 3 0.17 4.26% 46 4.02 1 0.03 0.81% 2 0.11 3.00% 38 3.51
  TOTAL 1 0.03 0.71% 3 0.17 4.26% 46 4.02 1 0.03 0.81% 2 0.11 3.00% 38 3.51
TOTAL a 76 7.69 6.41% 299 33.26 27.72% 1154 119.97 22 2.89 8.13% 66 9.66 27.17% 295 35.55
  TOTAL 76 7.69 6.41% 299 33.26 27.72% 1154 119.97 22 2.89 8.13% 66 9.66 27.17% 295 35.55

 

This is an output I got using Proc Report. In the modification, I want to remove the column count wherever  'N+R'n Column occurs.

Also in this output, what is the blank space above column CYCLE and PRODUCT and is on the left of TEAM 1, as this column is generated in PROC REPORT, and I want to split those two blank cells into 4 as happening below in column CYCLE and PRODUCT so that I can format it into excel with other reports as  I want to decrease the cell size of cycle, but cell size reduction should only be applied to this report and not on other reports which are also being printed on the same sheet. I have printed my PROC REPORT below. 

Can I get a solution for both of my problems? Thank you!

Please let me know if it is difficult to understand.

 

PROC REPORT DATA=BKT2_HL_&mnth._&nextmnth.5(WHERE=('STATUS 2'N NE 'Not Resolved')) OUT=WO_NR;
COLUMNS CYCLE1 PRODUCT_CLASS FINAL_ALLOCATION, ("status 2"n,(COUNT TOTAL_POS POS_PER));
DEFINE CYCLE1/nozero 'CYCLE' ORDER=FORMATTED STYLE={width=90PT FONTSIZE=2 BORDERWIDTH=1 BORDERCOLOR=BLACK JUST=CENTER BACKGROUND=WHITE FOREGROUND=BLACK } group STYLE(HEADER)={FONT_WEIGHT=BOLD FONTSIZE=2 BACKGROUND=#000080 FOREGROUND=WHITE};
DEFINE PRODUCT_CLASS/nozero 'PRODUCT' ORDER=FORMATTED STYLE={width=110PT FONTSIZE=2 BORDERWIDTH=1 BORDERCOLOR=BLACK JUST=CENTER BACKGROUND=WHITE FOREGROUND=BLACK } group STYLE(HEADER)={FONT_WEIGHT=BOLD FONTSIZE=2 BACKGROUND=#000080 FOREGROUND=WHITE};
DEFINE FINAL_ALLOCATION/nozero STYLE={FONTSIZE=2 BORDERWIDTH=1 BORDERCOLOR=BLACK} across '' STYLE(HEADER)={FONT_WEIGHT=BOLD FONTSIZE=2 BORDERWIDTH=1 BORDERCOLOR=BLACK BACKGROUND=#000080 FOREGROUND=WHITE};
DEFINE "status 2"n/nozero STYLE={FONTSIZE=2 BORDERWIDTH=1 BORDERCOLOR=BLACK} across  '' STYLE(HEADER)={FONT_WEIGHT=BOLD FONTSIZE=2 BORDERWIDTH=1 BORDERCOLOR=BLACK BACKGROUND=#FF6600 FOREGROUND=WHITE};
DEFINE TOTAL_POS/nozero STYLE={ FONTSIZE=2 JUST=CENTER VJUST=MIDDLE BORDERWIDTH=1 BORDERCOLOR=BLACK BACKGROUND=WHITE FOREGROUND=BLACK} 'POS' ANALYSIS SUM FORMAT=6.2 STYLE(HEADER)={FONT_WEIGHT=BOLD FONTSIZE=2 BORDERWIDTH=1 BORDERCOLOR=BLACK BACKGROUND=#000080 FOREGROUND=WHITE};
DEFINE COUNT/nozero STYLE={FONTSIZE=2 JUST=CENTER VJUST=MIDDLE BORDERWIDTH=1 BORDERCOLOR=BLACK BACKGROUND=WHITE FOREGROUND=BLACK} 'Count' ANALYSIS SUM FORMAT=6. STYLE(HEADER)={FONT_WEIGHT=BOLD FONTSIZE=2 BORDERWIDTH=1 BORDERCOLOR=BLACK BACKGROUND=#000080 FOREGROUND=WHITE};
DEFINE POS_SUM/NOPRINT;
DEFINE POS_PER/nozero 'POS%' ANALYSIS SUM FORMAT=PERCENT8.2 STYLE={ FONTSIZE=2 JUST=CENTER VJUST=MIDDLE BORDERWIDTH=1 BORDERCOLOR=BLACK BACKGROUND=WHITE FOREGROUND=BLACK} STYLE(HEADER)={FONT_WEIGHT=BOLD FONTSIZE=2 BORDERWIDTH=1 BORDERCOLOR=BLACK BACKGROUND=#000080 FOREGROUND=WHITE};
compute PRODUCT_CLASS;
if PRXMATCH('%TOTAL%',PRODUCT_CLASS) then
call define (_row_,"style","style={BORDERBOTTOMWIDTH=1 FONT_WEIGHT=BOLD background=#FF6600 FOREGROUND=WHITE}");
endcomp;
run;

 

2 REPLIES 2
PaigeMiller
Diamond | Level 26

Also in this output, what is the blank space above column CYCLE and PRODUCT and is on the left of TEAM 1, as this column is generated in PROC REPORT, and I want to split those two blank cells into 4 as happening below in column CYCLE and PRODUCT so that I can format it into excel with other reports as  I want to decrease the cell size of cycle, but cell size reduction should only be applied to this report and not on other reports which are also being printed on the same sheet.

 

Since you specifically asked if it was difficult to understand, I state that the above run-on sentence is difficult to understand.

 

I think I understand the last part ... you want cell size (I assume you mean column width) to have a certain width in table 1 of a single Excel tab and a different width in table 2 of that same Excel tab, with Table 1 located above Table 2 in the Excel tab. This is not possible. You can't do it in Excel by manually changing the column widths of part of the tab, and you can't do it with SAS.

 

I didn't understand the rest, and anyway it would help if you showed us whatever it is you want, along with making the writing more clear.

--
Paige Miller
Cynthia_sas
SAS Super FREQ

Hi:

  I agree with @PaigeMiller your description is hard to understand. However, I think I understand part of it:

Cynthia_sas_0-1719421658875.png

Your report has 3 report "header" rows, I have highighted the TEAM1/TEAM2 row in yellow and I have highlighted the N+R/Resolved/TOTAL rows in cyan and I have highlighted the final row with the CYCLE/PRODUCE/Count/POS/POS% ... in light purple. This is the PROC REPORT default way to format rows that where you do not have any text on row 1 (yellow) or row 2 (cyan). PROC REPORT puts empty cells above CYCLE and PRODUCT. You cannot merge those cells with PROC REPORT (the same way you can merge the cells in Excel. So if your destination of choice (which you did not show) is ODS EXCEL, then if you want these cells merged, you'll need to do it in Excel, after SAS creates the output file.

  I'm not sure I understand your code and you did not provide any data to test with. You have a LOT of ACROSS variables, with items nested under the ACROSS variables. I don't think you can do what you want by suppressing the COUNT under some columns, but I don't really understand what you want to do. So I'm just offering advice about the blank cells above CYCLE and PRODUCT.

Cynthia

 

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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
  • 860 views
  • 0 likes
  • 3 in conversation