Hello,
I've never used tagesets.excelXP format, so I am confusing how to adjust output table.
I used the below code to produce report and it looks like
---------------------------------------
ods tagsets.ExcelXP path= '\\c\'
file= "Performance Weekly Report from &P_DATE to &C_DATE..xml" style=analysis;
proc report data=WORK.REPORT_V2;
col dispatcher_id 'hour released'n avg_nj_release avg_jeppesen_release avg_hardcopy;
define dispatcher_id / group style(Column)={width=2in just=center} 'ID';
define 'hour released'n / display 'HOUR RELEASED';
define avg_nj_release / display 'TOTAL RELEASE';
define avg_jeppesen_release / display 'JEP RELEASE';
define avg_hardcopy / display 'COPY';
run;
quit;
ods tagsets.ExcelXP close;
-------------------------------------------------------
ID | HOUR RELEASED | TOTAL RELEASE | JEP RELEASE | COPY |
acecchet | 0 | 0 | 0 | 0 |
1 | 0 | 0 | 0 | |
2 | 0 | 0 | 0 | |
3 | 0 | 0 | 0 | |
4 | 0 | 0 | 0 | |
5 | 0 | 0 | 0 | |
6 | 2 | 0 | 0 | |
7 | 6.2 | 0 | 0 | |
8 | 3.6 | 0 | 0 | |
9 | 6.8 | 0 | 0 | |
10 | 5.8 | 0 | 0 | |
11 | 5.8 | 0 | 0 | |
12 | 3 | 0 | 0 | |
13 | 4 | 0 | 0 | |
14 | 1.33 | 0 | 0 | |
15 | 0 | 0 | 0 | |
16 | 0 | 0 | 0 | |
17 | 0 | 0 | 0 | |
18 | 0 | 0 | 0 | |
19 | 0 | 0 | 0 | |
20 | 0 | 0 | 0 | |
21 | 0 | 0 | 0 | |
22 | 0 | 0 | 0 | |
23 | 0 | 0 | 0 |
===============================
I would like to change the above table as below.1. Merge the first column based on ID name. 2. I would like to put background color in the column title (i.e row 1)
ID | HOUR RELEASED | TOTAL RELEASE | JEP RELEASE | COPY |
acecchet | 0 | 0 | 0 | 0 |
1 | 0 | 0 | 0 | |
2 | 0 | 0 | 0 | |
3 | 0 | 0 | 0 | |
4 | 0 | 0 | 0 | |
5 | 0 | 0 | 0 | |
6 | 2 | 0 | 0 | |
7 | 6.2 | 0 | 0 | |
8 | 3.6 | 0 | 0 | |
9 | 6.8 | 0 | 0 | |
10 | 5.8 | 0 | 0 | |
11 | 5.8 | 0 | 0 | |
12 | 3 | 0 | 0 | |
13 | 4 | 0 | 0 | |
14 | 1.33 | 0 | 0 | |
15 | 0 | 0 | 0 | |
16 | 0 | 0 | 0 | |
17 | 0 | 0 | 0 | |
18 | 0 | 0 | 0 | |
19 | 0 | 0 | 0 | |
20 | 0 | 0 | 0 | |
21 | 0 | 0 | 0 | |
22 | 0 | 0 | 0 | |
23 | 0 | 0 | 0 |
Would you please know how I can change these features?
Thank you for all comments in advance.
It is weird. Can you give us some data to test .
Or try SPANROWS options :
proc report data=WORK.REPORT_V2 nowd spanrows;
col dispatcher_id 'hour released'n avg_nj_release avg_jeppesen_release avg_hardcopy;
define dispatcher_id / group style(Column)={width=2in just=center} 'ID';
It is weird. Can you give us some data to test .
Or try SPANROWS options :
proc report data=WORK.REPORT_V2 nowd spanrows;
col dispatcher_id 'hour released'n avg_nj_release avg_jeppesen_release avg_hardcopy;
define dispatcher_id / group style(Column)={width=2in just=center} 'ID';
Thank you for the comment.
Spanrows command is working fine.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.