data _null_ color code values or revamp using proc report

Rob123 — Fri, 02 Nov 2018 13:30:51 GMT

Hi All,

i am generating a report using data _null_. Trying to color code the rows('~R"\cf2 "'||strip(LBDTC);) and also individual values in dataset(&invar ='~R"{\cf5 "'||strip(&invar.)||'~R"}"';). because of the extra rtf code there is not enough space in the page and the values are being overwritten. please see attached blue highlighted row. i tired calculating the length but it seems very difficult(commented out part in the attached code under c2=0; c3=0 etc). is there a way to resolve this issue and color code values without having to calculate the lengths for the additional rtf code(eg: ('~R"\cf2 "'||strip(LBDTC);)

As an alternative, how do i generate the same output using proc report.

- i can generate proc report for SP HEADER1 and SP HEADER2(please see attached file). but how do i make the extra columns go below(pink highlight) and how to create the yellow highlight text. on page 2 the text changes if the data continues.

Note: highlighting is done only to help with understanding. but, blue and pink font(value color) is what is needed.

Thanks for all your help!

%macro xx;
title ' ';
option byline nodate nonumber missing=' ' pageno=1 center ls = 256 ps=100 orientation="landscape";
ods listing close;
ods rtf file = "output\&rtfname..rtf" wordstyle = '{\s15 caption;}' style = bnptemp.rtf;
ods escapechar="~";

****Start Data Null Step****;
data _null_ xx;
	set imwgtbl(where = (&whr.) ) end=eof;
	by usubjid cat adt avisitn avisit lbdtc;
	file print header=h n=ps ll=ll line=l notitles ps=55 ls=256;
	retain pnum 0;
	if first.usubjid then pnum=0;
	if first.usubjid then put _page_;

	*** new record ***;
	if rectype='NEW' then do;
		LBDTC = '~R"\cf2 "'||strip(LBDTC);
		nw = 9;
	end;
	else if rectype='CHG' then do;
		LBDTC = '~R"\cf5 "'||strip(LBDTC);
		nw = 9;
	end;
	else nw=0;
	nw=0;

	select (cat);
		when (   1  ) do;
			if first.cat then do;
				PUT @1 h1;
				PUT @1 h2;
				PUT @1 h3;
				PUT @1 h4;
				PUT @1 h5;
				PUT @1 h6;
				PUT @1 &ulen*'_';
			end;
		end;

		when (   2  ) do;
			if first.cat then put  @70 "---------------------------Kidney---------------------";
			if first.cat then put  @30 "---------------Liver-------------       _________Urine_______        _______Serum eGFR_______" /;
			else if (not first.cat and L=7) then do;
				PUT @1 "Liver and kidney (continued)";
				put  @70 "---------------------------Kidney---------------------";
				put  @30 "---------------Liver-------------       _________Urine_______        _______Serum eGFR_______" /;
			end;

			if first.cat or (not first.cat and L=11) then do;
				PUT @1 "Date"  @14 "Visit"   @30 "Alkaline "        @46 "INC "             @58 "DEC " 				    @70 "Proteinuria"     @86 "DEC"  								@100 "eGFR (mL/min"   				 @118 "DEC";
				PUT @1 " "     @14 " "       @30 "Phosph.(U/L)"     @46 "(%)~{super a}"    @67 "(%)~{super b}"		@90 "(g/24 h)"     	  @105 "(%)~{super b}"      @129 "per 1.73 m~{super 2})"   @156 "(%)~{super b}";
				PUT @1 &ulen*'_';
			end;
			*** column increment ***;
			 
			c2=0; c3=0; c4=0; c5=0; c6=0; c7=0; c8=0; c9=0; c10=0;
/*			if ^missing(c_LBDTC) then do; c2=c2+10; c3=c3+10; c4=c4+10; c5=c5+10; c6=c6+10; c7=c7+10; c8=c8+10; c9=c9+10; c10=c10+10; end;*/
/*			if ^missing(c_AVISIT) then do; c3=c3+10; c4=c4+10; c5=c5+10; c6=c6+10; c7=c7+10; c8=c8+10; c9=c9+10; c10=c10+10; end;*/
/*			if ^missing(c_l1) then do; c4=c4+10; c5=c5+10; c6=c6+10; c7=c7+10; c8=c8+10; c9=c9+10; c10=c10+10; end;*/
/*			if ^missing(c_l2) then do; c5=c5+10; c6=c6+10; c7=c7+10; c8=c8+10; c9=c9+10; c10=c10+10; end;*/
/*			if ^missing(c_l3) then do; c6=c6+10; c7=c7+10; c8=c8+10; c9=c9+10; c10=c10+10;  end;*/
/*			if ^missing(c_k1) then do; c7=c7+10; c8=c8+10; c9=c9+10; c10=c10+10; end;*/
/*			if ^missing(c_k2) then do; c8=c8+10; c9=c9+10; c10=c10+10; end;*/
/*			if ^missing(c_k3) then do; c9=c9+10; c10=c10+10; end;*/
/*			if ^missing(c_k4) then do; c10=c10+10; end;*/

/*			if index(l1,'super') then do; c4=+9.8; c5=+9.8; c6=c6+9.8; c7=c7+9.8; c8=c8+9.8; c9=c9+9.8; c10=c10+9.8; end;*/
/*			if index(l2,'super') then do; c5=+9.8; c6=c6+9.8; c7=c7+9.8; c8=c8+9.8; c9=c9+9.8; c10=c10+9.8; end;*/
/*			if index(l3,'super') then do; c6=c6+9.8; c7=c7+9.8; c8=c8+9.8; c9=c9+9.8; c10=c10+9.8;  end;*/
/*			if index(k1,'super') then do; c7=c7+9.8; c8=c8+9.8; c9=c9+9.8; c10=c10+9.8; end;*/
/*			if index(k2,'super') then do; c8=c8+9.8; c9=c9+9.8; c10=c10+9.8; end;*/
/*			if index(k3,'super') then do; c9=c9+9.8; c10=c10+9.8; end;*/
/*			if index(k4,'super') then do; c10=c10+9.8; end;*/

/*			PUT  @1 LBDTC  @20+nw+c2 AVISIT   @32+nw+c3 l1   @57+nw+c4 l2     @79+nw+c5 l3    @105+nw+c6 k1    @130+nw+c7 k2     @150+nw+c8 k3     @176+nw+c9 k4 ;*/
				PUT  @1 LBDTC  @14+nw+c2 AVISIT   @30+nw+c3 l1   @55+nw+c4 l2     @67+nw+c5 l3    @81+nw+c6 k1    @104+nw+c7 k2     @120+nw+c8 k3     @145+nw+c9 k4 ;
		end;

																																									 
		when (   3  ) do;
			if first.cat then PUT @30 "------------------------------------------------Heart------------------------------------------------";
			if first.cat then put @27 "____________NT-proBNP_________       _______Hs-trop______      _______EF______        ______NYHA_______"/;
			else if (not first.cat and L=7) then do;
					put @1 "Heart (continued)";
					put @30 "------------------------------------------------Heart------------------------------------------------";
					put @27 "____________NT-proBNP_________       _______Hs-trop______      _______EF______       _______NYHA_______"/;
			end;

			if first.cat or (not first.cat and L=11) then do;
				PUT @1 "Date"  @14 "Visit"   @27 "Val."    @40 "INC"   			       @52 "DEC"   			  		@65 "Val."    @78 "INC"   		 		 @90 "Val."    @102 "DEC"  							@113 "Val."  @126 "DEC~{super c}";
				PUT @1 " "     @14 " "       @27 "(ng/L)"  @40 "(%)~{super a}"     @61 "(%)~{super b}"    @83 "(ng/L)"  @97 "(%)~{super a}"  @117 " (%)"   @130 "(%)~{super b}"     /*@135 " "   @140 " "*/;
				PUT @1 &ulen*'_';
			end;
			*** column increment ***;
			c2=0; c3=0; c4=0; c5=0; c6=0; c7=0; c8=0; c9=0; c10=0; c11=0; 
/*			if ^missing(c_LBDTC) then do; c2=c2+15; c3=c3+15; c4=c4+15; c5=c5+15; c6=c6+15; c7=c7+15; c8=c8+15; c9=c9+15; c10=c10+15; c11=c11+15; end;*/
/*			if ^missing(c_AVISIT) then do; c3=c3+15; c4=c4+15; c5=c5+15; c6=c6+15; c7=c7+15; c8=c8+15; c9=c9+15; c10=c10+15; c11=c11+15; end;*/
/*			if ^missing(c_t1) then do; c4=c4+15; c5=c5+15; c6=c6+15; c7=c7+15; c8=c8+15; c9=c9+15; c10=c10+15; c11=c11+15; end;*/
/*			if ^missing(c_t2) then do; c5=c5+15; c6=c6+15; c7=c7+15; c8=c8+15; c9=c9+15; c10=c10+15; c11=c11+15;end;*/
/*			if ^missing(c_t3) then do; c6=c6+15; c7=c7+15; c8=c8+15; c9=c9+15; c10=c10+15; c11=c11+15; end;*/
/*			if ^missing(c_t4) then do; c7=c7+15; c8=c8+15; c9=c9+15; c10=c10+15; c11=c11+15; end;*/
/*			if ^missing(c_t5) then do; c8=c8+15; c9=c9+15; c10=c10+15; c11=c11+15; end;*/
/*			if ^missing(c_t6) then do; c9=c9+15; c10=c10+15; c11=c11+15; end;*/
/*			if ^missing(c_t7) then do; c10=c10+15; c11=c11+15; end;*/
/*			if ^missing(c_t8) then do; c11=c11+15; end;*/

/*			if index(t1,'super')^=0 then do; c4=c4+10; c5=c5+10; c6=c6+10; c7=c7+10; c8=c8+10; c9=c9+10; c10=c10+10; c11=c11+10; end;*/
/*			if index(t2,'super')^=0 then do; c5=c5+10; c6=c6+10; c7=c7+10; c8=c8+10; c9=c9+10; c10=c10+10; c11=c11+10;end;*/
/*			if index(t3,'super')^=0 then do; c6=c6+10; c7=c7+10; c8=c8+10; c9=c9+10; c10=c10+10; c11=c11+10; end;*/
/*			if index(t4,'super')^=0 then do; c7=c7+10; c8=c8+10; c9=c9+10; c10=c10+10; c11=c11+10; end;*/
/*			if index(t5,'super')^=0 then do; c8=c8+10; c9=c9+10; c10=c10+10; c11=c11+10; end;*/
/*			if index(t6,'super')^=0 then do; c9=c9+10; c10=c10+10; c11=c11+10; end;*/
/*			if index(t7,'super')^=0 then do; c10=c10+10; c11=c11+10; end;*/
/*			if index(t8,'super')^=0 then do; c11=c11+10; end;*/

/*			PUT  @1 LBDTC  @20+nw+c2 AVISIT  @30+nw+c3 t1  @42+nw+c4 t2  @58+nw+c5 t3   @74+nw+c6 t4  @88+nw+c7 t5  @102+nw+c8 t6  @116+nw+c9 t7  @130+nw+c10 t8  @144+nw+c11 t9 ;*/
				PUT  @1 LBDTC  @14+nw+c2 AVISIT  @26+nw+c3 t1  @50+nw+c4 t2  @61+nw+c5 t3   @73+nw+c6 t4  @97+nw+c7 t5  @109+nw+c8 t6  @130+nw+c9 t7  @153+nw+c10 t8  @170+nw+c11 t9 ;
		end;

		otherwise;
	end;
	if last.cat then put ;

	IF last.usubjid or LL <= 17 THEN LINK FOOT; ** if pagebreaks then increase the value **;

	/* RETURN statement is necessary before the label */
	/* to prevent the header from executing for each  */
	/* iteration of the DATA step.                    */

	RETURN;

	FOOT:
		PUT @1 &ulen*'_' ;
		if last.usubjid and "&environ" ne "PROD" then PUT @1 '[Draft]' @123 "&IRC_CUTDT.";
		else if not last.usubjid and "&environ" ne "PROD" then PUT @1 '[Draft]'  @123 "(Continued)";
		else if not last.usubjid and "&environ" eq "PROD" then PUT @123 "(Continued)";
		if not last.usubjid then put _page_;
	RETURN;

	H:
		IF _N_=1 THEN PNUM=0;
		PNUM = PNUM +1;
		PUT #1 @127 'Page ' pnum 2.;
		PUT /@1 "[xxxx] yyyyy Listing; ITT Population (Study zzzzzzzz)"/ @1 &ulen*'_' /;
		if not first.usubjid then put @1 "Unique Subject Identifier:" +1  usubjid;
		
	RETURN;
run;

ods rtf close;
%mend xx;

Re: data _null_ color code values or revamp using proc report

Rob123 — Fri, 02 Nov 2018 13:37:57 GMT

Attachment did not go through. trying again

Re: data _null_ color code values or revamp using proc report

RW9 — Fri, 02 Nov 2018 14:17:16 GMT

I don't see anything in that report that could not be done with a few proc report steps, no page break, and some inline formatting. Looks like 3 proc report blocks - turn off page breaking between them -

ods startpage= now | never | yes | no;

Inline styles are done using:

ods escapechar="^";

I use the carat, but you can use any symbol. You can then add inline styles like;

"This text ^s={font=bold}now bold";

You can also use foreground/background color on column headers:

http://www2.sas.com/proceedings/forum2008/224-2008.pdf

Also, you can add in RTF code directly using the escape char as well:

"Some text ^r{/bold/}other text"

I really wouldn't want to, in this day and age, be going back coding all the output myself.

Oh, and if you really want full control, have a look at the tagset rtf and style templates available. You can set loads of parameters in the style, and by altering the various tagsets change the way things are output.

Will I do this for you, not unless you pay me!

Re: data _null_ color code values or revamp using proc report

Rob123 — Tue, 06 Nov 2018 02:50:13 GMT

Thank you RW9.

Re: data _null_ color code values or revamp using proc report

ChrisNZ — Tue, 06 Nov 2018 03:41:30 GMT

You could be interested in the dedicated object that does this sort of things.

https://communities.sas.com/t5/ODS-and-Base-Reporting/The-DATA-step-ODSOUT-object/td-p/87686

http://support.sas.com/resources/papers/proceedings10/072-2010.pdf

topic Re: data _null_ color code values or revamp using proc report in SAS Programming

data _null_ color code values or revamp using proc report

Re: data _null_ color code values or revamp using proc report

Re: data _null_ color code values or revamp using proc report

Re: data _null_ color code values or revamp using proc report

Re: data _null_ color code values or revamp using proc report