topic Re: Formatting missing values in PROC REPORT in SAS Procedures

Formatting missing values in PROC REPORT

rlafond — Fri, 20 Jul 2018 14:05:43 GMT

I'm using the below code to create a report. The output looks great, but I need to apply formatting to my missing values, specifically for Paid and Percent. The non-zero data was formatted earlier during a SQL procedure. So far I've tried restating the format with DEFINE and also with FORMAT after define - neither worked.

Any ideas?

Thank you,

Ryan

ods escapechar="^";

options nodate label orientation=portrait nobyline missing=0;

ods _all_ close;

ods pdf file = "\\TEST.pdf"

style=htmlblue;

PROC REPORT DATA=WORK.STEP2;

COLUMN TT_Description NetworkType, (Claims Paid Percent);

define TT_Description / group "Treatment Type";

define NetworkType / " " across;

define Claims / analysis sum;

define Paid / analysis sum;

define Percent / analysis sum;

RUN;

ODS PDF CLOSE;

Re: Formatting missing values in PROC REPORT

PaigeMiller — Fri, 20 Jul 2018 14:08:36 GMT

@rlafond wrote:

I'm using the below code to create a report. The output looks great, but I need to apply formatting to my missing values, specifically for Paid and Percent. The non-zero data was formatted earlier during a SQL procedure. So far I've tried restating the format with DEFINE and also with FORMAT after define - neither worked.

Format the missings to what?

In any event, you can define a format in PROC FORMAT that includes the desired formatting for missing values before your run PROC REPORT, and then apply the format in PROC REPORT.

Re: Formatting missing values in PROC REPORT

rlafond — Fri, 20 Jul 2018 14:10:17 GMT

I want missing values for Paid to be dollar14.2 and Percent to be Percent5.1.

How would I write a PROC FORMAT to target missing values in PROC REPORT?

Thanks,

Ryan

Re: Formatting missing values in PROC REPORT

ballardw — Fri, 20 Jul 2018 14:33:03 GMT

@rlafond wrote:

I want missing values for Paid to be dollar14.2 and Percent to be Percent5.1.

How would I write a PROC FORMAT to target missing values in PROC REPORT?

Thanks,

Ryan

And what exactly does a missing value in dollar14.2 look like? "$ .00" "$**********.**" something else?

Same with what does a missing value in Percent5.1 look like?

BTW you may want to either consider using PercentN5.1 or make the width wider as Percent5.1 does not show 100 percent very well:

data example;
   input x;
datalines;
1
.1
.01
-.1
-.01
;
run;
proc print;
   format x percent5.1;
run;

Re: Formatting missing values in PROC REPORT

PaigeMiller — Fri, 20 Jul 2018 14:35:31 GMT

@rlafond wrote:

I want missing values for Paid to be dollar14.2 and Percent to be Percent5.1.

How would I write a PROC FORMAT to target missing values in PROC REPORT?

No, you want the non-missing values for PAID to be dollar14.2 and the non-missing values for PERCENT to be percent5.1.

So we still don't know what you want the missing values to look like. Show us. Type it in what the missing values are supposed to look like.

Re: Formatting missing values in PROC REPORT

rlafond — Fri, 20 Jul 2018 15:36:03 GMT

Missing values for Paid should look like $0.00 and missing values for Percent should look like 0.0%.

Re: Formatting missing values in PROC REPORT

PaigeMiller — Fri, 20 Jul 2018 15:39:21 GMT

SO before your PROC REPORT

proc format;
    value paidf .='$0.00' other=[dollar14.2];
run;

You can figure out what to do for percent, it's similar.

Then, in PROC REPORT

define paid / analysis sum format=paidf.;