Hi Cynthia, I am attaching sample data. And you are right in regards to HEADLINE, HEADSKIP – they do nothing in my excel output. I simply forgot to remove them. I am new to SAS EG and ODS is a brand new territory for me – there is soo much to ODS and I was trying everything and anything that could help me in developing this report. There is much more code than what I shared here. However since my question only pertained to across variable and my workaround utilizing LINE statement, I tried to streamline the code and only show the piece I still have not found an answer for. Everything else however, works. My second print screen in the original message I posted is the actual output generated in SAS. A larger sample is shown below, LINE statement with REPEAT will account for printing on the output only ‘needed’ quarters (minimum of 5 quarters, maximum of 8, depending when the report is run during the year): Values in COL1 thru COL8 were originally values under FNL_TIMEFRAME; however having those there, generated additional ‘across’ columns which I did not need. Therefore I used a TRANSPOSE function in the code to create COL1 thru COL8 and used them in proc report’s LINE statement. The only drawback of using LINE @ statement is that it does not ‘scale’ the same way as any other heading when a sheet needs to be zoom-in or zoom-out. If there is a workaround to that or if FNL_TIMEFRAME could have different values from one section of the report to another w/out generating additional ‘across’ columns, that any of you are aware of and could share that knowledge with me, I would greatly appreciate. Thank you for your time and assistance!!! PROC REPORT DATA=WORK.RPT1 NOWD HEADLINE HEADSKIP SPLIT= '*'
COLUMN KEYCOL SEC COL2 COL3 COL4 COL5 COL6 COL7 COL8
REC FNL_CATEGORY RPTIMES FNL_TIMEFRAME,(' ' FNL_MEASURE1 FNL_MEASURE2 FILLER);
DEFINE KEYCOL / GROUP PAGE NOPRINT;
DEFINE SEC / GROUP NOPRINT;
DEFINE COL2 / GROUP NOPRINT;
DEFINE COL3 / GROUP NOPRINT;
DEFINE COL4 / GROUP NOPRINT;
DEFINE COL5 / GROUP NOPRINT;
DEFINE COL6 / GROUP NOPRINT;
DEFINE COL7 / GROUP NOPRINT;
DEFINE COL8 / GROUP NOPRINT;
DEFINE REC / GROUP NOPRINT;
DEFINE FNL_CATEGORY / GROUP ' ' LEFT
STYLE(COLUMN)={JUST=L TAGATTR='WRAPTEXT:NO' WIDTH=100%};
DEFINE RPTIMES / GROUP MISSING NOPRINT; /* repeat times */
DEFINE FNL_TIMEFRAME / ACROSS ' ' ORDER=DATA LEFT
STYLE(COLUMN)={JUST=L TAGATTR='WRAPTEXT:NO' WIDTH=100%};
DEFINE FNL_MEASURE1 / ANALYSIS 'Dollars';
DEFINE FNL_MEASURE2 / ANALYSIS 'PPPM';
DEFINE FILLER / NOZERO ' '
STYLE(COLUMN)={JUST=R COLOR=WHITE TAGATTR='FORMAT:#,##0.00_);[RED]\(#,##0.00\)'};
COMPUTE AFTER SEC / STYLE={JUST=L FONT_FACE='Arial Unicode MS' FONT_SIZE=10pt FONT_WEIGHT=BOLD HEIGHT=10pt};
LENGTH TEXT1 $196;
LENGTH TEXT2 $294;
IF SEC = 1
THEN
DO;
IF RPTIMES LE 4
THEN TEXT1 = CAT(COL2,COL3,COL4,COL5);
ELSE IF RPTIMES = 5
THEN TEXT1 = CAT(COL2,COL3,COL4,COL5,COL6);
ELSE IF RPTIMES = 6
THEN TEXT1 = CAT(COL2,COL3,COL4,COL5,COL6,COL7);
ELSE IF RPTIMES = 7
THEN TEXT1 = CAT(COL2,COL3,COL4,COL5,COL6,COL7,COL8);
NUM1 = 28*RPTIMES;
TEXT2 = REPEAT(' $M PPPM',RPTIMES);
NUM2=42*RPTIMES;
END;
ELSE
DO;
TEXT1 = '';
NUM1=0;
TEXT2 = '';
NUM2=0;
END;
LINE @122 TEXT1 $VARYING. NUM1;
LINE @110 TEXT2 $VARYING. NUM2;
ENDCOMP;
RUN;
... View more