topic Re: Multiple formats on one variable in proc report in SAS Procedures

Multiple formats on one variable in proc report

sarahsasuser — Fri, 15 Aug 2014 15:21:37 GMT

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;

Re: Multiple formats on one variable in proc report

Reeza — Fri, 15 Aug 2014 15:30:24 GMT

Create a custom nested format and use that instead:

proc format;

format sarah_custom_fmt

. = 'ND'

other=[percent6.2];

run;

Re: Multiple formats on one variable in proc report

jwillis — Fri, 15 Aug 2014 15:43:28 GMT

"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.

Re: Multiple formats on one variable in proc report

Cynthia_sas — Fri, 15 Aug 2014 16:10:05 GMT

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;

Re: Multiple formats on one variable in proc report

sarahsasuser — Fri, 15 Aug 2014 16:48:08 GMT

HI All,

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

Sarah

Re: Multiple formats on one variable in proc report

jwillis — Fri, 15 Aug 2014 17:17:30 GMT

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

Re: Multiple formats on one variable in proc report

ballardw — Fri, 15 Aug 2014 18:53:02 GMT

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.

Re: Multiple formats on one variable in proc report

Cynthia_sas — Fri, 15 Aug 2014 23:42:32 GMT

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;

Re: Multiple formats on one variable in proc report

Ksharp — Sat, 16 Aug 2014 13:40:41 GMT

another way is

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

Xia Keshan

Re: Multiple formats on one variable in proc report

jwillis — Mon, 18 Aug 2014 11:35:53 GMT

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

Re: Multiple formats on one variable in proc report

Tom — Mon, 18 Aug 2014 12:35:26 GMT

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.