topic Re: Proc report - using preloadfmt with conditional formatting in SAS Procedures

Proc report - using preloadfmt with conditional formatting

Phase2 — Wed, 31 Jan 2018 20:29:01 GMT

Sorry if this a duplicate posting, I tried to edit my previous version of this question and I can no longer find it so I'm asking again.

Anyway, I am using SAS v 9.4 and I want to create a report that displays every level of a format that is being applied. I am conditionally applying formats to a secondary group variable based on the value of a primary group variable and I want each value of the appropriate format to appear in the table.

Here is an example data set:

data temp;
	input score unscorable bad segment $ i;
datalines;
. 1 1 B 1
. 1 0 B 1
. 1 1 B 1
0 1 0 A 1
0 1 1 A 1
0 1 1 A 1
500 0 1 A 1
501 0 1 B 1
501 0 0 A 1
501 0 1 A 1
503 0 0 A 1
503 0 1 A 1
504 0 0 B 1
505 0 0 B 1
506 0 1 B 1
507 0 0 B 1
508 0 0 A 1
509 0 0 B 1
510 0 0 A 1
510 0 0 B 1
510 0 0 A 1
;
run;

These are the two formats I am using:

proc format;
	value scoref
		500-501 = "500-501"
		502-503 = "502-503"
		504-505 = "504-505"
		506-507 = "506-507"
		508-509 = "508-509"
		510-511 = "510-511"
		512-513 = "512-513"
	;
	value unscf
		. = "."
		0 = "0"
	;
run;

And here is my proc report:

title "Overall";
proc report data=temp missing;
	column unscorable _unscorable score i bad;

	define unscorable / group noprint;
	define _unscorable / computed noprint;
	define score / group "Score";
	define i / analysis sum "#";
	define bad / analysis sum "# Bad";

	compute _unscorable;
		if unscorable not = ' ' then do;
			hold = unscorable;
		end;
		_unscorable = hold;
	endcomp;

	compute score;
		if _unscorable = 0 then do;
			call define('score', 'format', 'scoref.');
		end;
		else if _unscorable = 1 then do;
			call define('score', 'format', 'unscf.');
		end;
	endcomp;

	break after unscorable / ol ul summarize;
	rbreak after / ol ul summarize;
run;

Right now the "512-513" level of scoref is not being displayed in this report. What I would like is for all levels of scoref to show up when _unscorable = 0 and all levels of unscf to show up when _unscorable = 1.

Eventually, I am planning on creating two tables, one for segment = A and one for segment = B. I would like these tables to have the same number of rows.

I have tried using preloadfmt with completerows, but can't get it to work.

Thanks!

Re: Proc report - using preloadfmt with conditional formatting

Cynthia_sas — Wed, 31 Jan 2018 21:20:55 GMT

Hi, PROC REPORT has some fairly specific rules about using the preloadfmt option. for example, in this paper: http://support.sas.com/resources/papers/proceedings11/239-2011.pdf notice the use of PRELOADFMT option and then either COMPLETEROWS or COMPLETECOLS, depending on whether your format is for an ORDER or GROUP item or for an ACROSS item.

cynthia

Re: Proc report - using preloadfmt with conditional formatting

Phase2 — Wed, 31 Jan 2018 21:41:48 GMT

I have had preloadfmt and completerows working, but not in the style that I want.

I was originally trying to get the same result, but with a single format like this:

proc format;
	value scoref_orig
		500-501 = "500-501"
		502-503 = "502-503"
		504-505 = "504-505"
		506-507 = "506-507"
		508-509 = "508-509"
		510-511 = "510-511"
		512-513 = "512-513"
		. = "."
		0 = "0"
	;
run;

title "Overall";
proc report data=temp missing completerows;
	column unscorable score i bad;

	define unscorable / group noprint;
	define score / group "Score" format=scoref_orig. preloadfmt;
	define i / analysis sum "#";
	define bad / analysis sum "# Bad";

	break after unscorable / ol ul summarize;
	rbreak after / ol ul summarize;
run;

This worked, but it preloads the entire format for both unscorable = 1 and unscorable = 0. I would like just the values of unscf to be present when unscorable = 1.

If the same result could be achieved using a single format that would be ideal, but it seemed to me that splitting up the format and applying it separately might be easier to start.