ods excel proc report and ‘across’ variables

[RENATA1]

Can ‘analysis’ variables be outputted in the same columns (as opposed to generating additional columns) if the value of the ‘across’ variable changes from one section of the report to anather? For example:

My across variable (FNL_TIMEFRAME – final timeframe)depends on the selected time period for a report; it will display different quarter/year combinations based on date selection. For a second section of the report, that display is slightly different than for the first section of the report:

Right now, I have a workaround to produce the second set of heading using LINE statement and utilizing values in my COL2 thru COL8 variables as different value in the FNL_TIMEFRAME (ex. ytd 2017 2nd quarter vs. 2017 2nd quarter) yields creating additional columns. The disadvantage of using LINE statement is that it does not scales as regular headings if a page is zoom-in or zoom-out and as a result does not display under variables neatly.

I was wondering if there is a way to somehow have those different headings but not generating additional columns if the value of FNL_TIMEFEAME changes?

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;	/* report section */
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 = 2 
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;

[VDD]

Wow can you read that SAS code with ease or does it hurt you eyes searching the code.

Formatted datasteps and procedures are sure a lot easer to read and understand.

Would you please format your SAS code rather than posting it as a run on statement.

[RENATA1]

it was addad as sas code.

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;	/* report section */
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 = 2 
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;

[Cynthia_sas]

Hi:
Without data, nobody can test your code or replicate your results, so it will be hard to provide any constructive answers based on the code alone. Do you have some data you can share?

Otherwise, my only observations are that with ODS EXCEL, some of the options you specify will be ignored because they are LISTING only options and are likely to be ignored by ODS Excel. This includes options like:
HEADLINE, HEADSKIP, and then with your LINE statement, you'll probably find that the @122 and the @110 don't get used by ODS EXCEL and I'd be surprised if the REPEAT worked the way you intend.

However, without your actual data and your actual ODS statements, it's hard to make any further comment.
Cynthia

[RENATA1]

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;

[Cynthia_sas]

Hi:
I don't generally open XLSX or Word files. You also didn't post your ODS statements. Also, are you getting any error messages in the SAS Log when you run this? Any messages at all?

Cynthia