Help using Base SAS procedures

Multiple formats on one variable in proc report

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 106
Accepted Solution

Multiple formats on one variable in proc report

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;


Accepted Solutions
Solution
‎08-15-2014 12:10 PM
SAS Super FREQ
Posts: 8,864

Re: Multiple formats on one variable in proc report

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;


use_format_for_ND.png

View solution in original post


All Replies
Super User
Posts: 19,789

Re: Multiple formats on one variable in proc report

Posted in reply to sarahsasuser

Create a custom nested format and use that instead:

proc format;

format sarah_custom_fmt

. = 'ND'

other=[percent6.2];

run;

Regular Contributor
Posts: 217

Re: Multiple formats on one variable in proc report

Posted in reply to sarahsasuser

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

Solution
‎08-15-2014 12:10 PM
SAS Super FREQ
Posts: 8,864

Re: Multiple formats on one variable in proc report

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;


use_format_for_ND.png
Regular Contributor
Posts: 217

Re: Multiple formats on one variable in proc report

Posted in reply to Cynthia_sas

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

Super User
Posts: 11,343

Re: Multiple formats on one variable in proc report

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.

SAS Super FREQ
Posts: 8,864

Re: Multiple formats on one variable in proc report

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;

Regular Contributor
Posts: 217

Re: Multiple formats on one variable in proc report

Posted in reply to Cynthia_sas

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

Super User
Super User
Posts: 7,042

Re: Multiple formats on one variable in proc report

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. 

Frequent Contributor
Posts: 106

Re: Multiple formats on one variable in proc report

Posted in reply to sarahsasuser

HI All,

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

Sarah

Super User
Posts: 10,023

Re: Multiple formats on one variable in proc report

Posted in reply to sarahsasuser

another way is

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

Xia Keshan

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 1302 views
  • 3 likes
  • 7 in conversation