Multiple formats on one variable in proc report

[sarahsasuser]

Can I have multiple formats on a variable in proc report? I have some data that I want to show as a percentage so I use format=PERCENT6.2. I also want to missing values to show up as "ND" and created a format so that .='ND.' However, the following code does not work to turn missing values in 'ND.' How do I do this?

define numvar/display 'Number Variable' format=nodata. FORMAT=PERCENT6.2;

[Reeza]

Create a custom nested format and use that instead:

proc format;

format sarah_custom_fmt

. = 'ND'

other=[percent6.2];

run;

[jwillis]

"ND" is a character value.  A single dot is a numeric representation.  Percent6 is a numeric format.  The only way I see to do what you want is to render/output the numeric variable as a character variable in the percent6 format.  When the numeric dot value is encountered, it will be written/rendered as the character value ND.

[Cynthia_sas · Accepted solution]

Hi, You can, in fact, do what Reeza suggests. The syntax for her PROC FORMAT was incorrect (needs a VALUE statement), but here is a test program that you can run.

Cynthia

proc format;

value custfm

     . = 'ND'

    other=[percent9.2];

run;

data testit;

  infile datalines;

  input type $ num;

return;

datalines;

aaa 0.34

bbb 0.12

ccc 0.12

ddd 0.04

eee 0.23

fff .

ggg 0.11

hhh 0.04

iii .

;

run;

ods listing close;

title; footnote;

ods html file='c:\temp\testit.html';

proc report data=testit;

  column type num;

  define type /order;

  define num / f=custfm.

         style(column)={just=r};

  rbreak after / summarize;

run;

ods html close;

[jwillis]

Cynthia,

Thank you for all that you post.  Please explain the use of the brackets in the format using a Value statement.  I cannot (easily) find a description of the use of brackets in a value format.  Also, the documentation I found says that a value statement is for characters so I am confused why you would use a character format on a numeric field.  Are you letting SAS convert the number to a character?  If you will point me to the correct documentation/learning resource, I will be grateful.

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002473472.htm

[ballardw]

The use of brackets is to use another predefined format for that range of values.

A value statement is for character values IF you define it as so by starting the format name with $, otherwise the format is numeric. An you can have character and numeric formats with the same names except for the $ prefix.

[Cynthia_sas]

Hi, jwillis:

  To specifically address your question about the doc. I believe you were misinterpreting the statement in the doc. It says:

VALUE Statement

---

Creates a format that specifies character strings to use to print variable values.

Perhaps it could have been worded better -- the format specifies a character string (or label) to use for the display of the variable values (a format can be used for character variables or numeric variables. You list the variable values to be labeled on the left side of the = sign and you list the labels to be used or the character string for the label on the right side of the = sign.) But a format can be used for variables which are character type or numeric type.

  So, if I have a numeric variable called Otype whose internal values can be 1, 2 or 3, I can use the numeric format shown below. On the other hand, if I have a character variable called CODE, whose values could be OH, IN or MI, I might have a $STATE format and a $SILLY format to use to display those values:
     

proc format;

  value ordtyp 1='Retail'

               2='Catalog'

               3='Internet';

  value $state 'OH' = 'Ohio'

               'IN' = 'Indiana'

               'MI' = 'Michigan';

  value $silly 'OH' = 'Oh No, Mr. Bill'

               'IN' = 'In-A-Gadda-Da-Vida'

               'MI' = 'Mi Casa es Su Casa';

run;
    

  What is on the LEFT side of the equal sign is the variable value that you want displayed differently. What is on the RIGHT side of the equal sign is the character string or label that should be used for that specific value (or range of values). When I specify a variable VALUE for a numeric variable on the left side of the equal sign, the numeric values are unquoted and the format name does NOT start with a $. But when I specify a variable VALUE for a character variable, the character values are quoted on the left side of the equal sign.

  There's an example below.

Cynthia

data usefmt;

  infile datalines dlm=',' dsd;

  input name $ Otype state $ code $;

return;

datalines;

alan,1,OH,MI

barb,2,IN,OH

carl,3,MI,IN

;

run;

proc format;

  value ordtyp 1='Retail'

               2='Catalog'

               3='Internet';

  value $state 'OH' = 'Ohio'

               'IN' = 'Indiana'

               'MI' = 'Michigan';

  value $silly 'OH' = 'Oh No, Mr. Bill'

               'IN' = 'In-A-Gadda-Da-Vida'

               'MI' = 'Mi Casa es Su Casa';

run;

ods html file='c:\temp\useformat.html';

proc print data=usefmt;

  title 'Use Character and Numeric Formats';

  var name Otype state code;

  format Otype ordtyp. state $state. code $silly.;

run;

ods _all_ close;

[jwillis]

Thank you Cynthia.
May I assume that brackets in a format are the same as quotes?   other=[percent9.2];

[Tom]

Quotes are used in SAS to denote literal values. The brackets in the VALUE statement are used to tell SAS that what is inside is the NAME of another format to apply.  If you used quotes then it would just display the name of the format rather than use the format to display the value. 

[sarahsasuser]

HI All,

Thanks! I used Reeza's suggestion (correcting the value statement) and it worked. Thanks!

Sarah

[Ksharp]

another way is

if ...... then call define( _col_,'format', 'format=percent8.2' );

Xia Keshan