Help using Base SAS procedures

One Variable with Different Formats

Not applicable
Posts: 0

One Variable with Different Formats

I'm trying to replicate an Excel report in SAS. Across the top are the months of the year and going down are the state abbreviations. Each state has 4 rows of data. The 1st row is # of scans; the 2nd row is the target # of scans; the 3 row is the difference between actual and goal. All can be formatted as comma6. The problem comes in with the 4th row which is % of goal formatted to 2 decimal places. I'm using Proc Report and setting 4 different data sets into one prior to printing to get the results I need. How do I format my "Jan" variable as a mix of comma6. with the last row of each state being formatted as percent8.1?
Super Contributor
Super Contributor
Posts: 3,176

Re: One Variable with Different Formats

Posted in reply to deleted_user
PROC REPORT, similar to PROC PRINT (and some others) -- you must assign another SAS variable and use a different SAS format.

Scott Barry
SBBWorks, Inc.
Posts: 9,368

Re: One Variable with Different Formats

Posted in reply to deleted_user
Unlike many other SAS procedures, PROC REPORT has the call define statement, that allows you to change a format when some condition is met or in the instance where you want most of the rows to show one format, but want a different row or a summary row to have dollar signs or currency indicators or, as in your case, a percent sign:

ods listing;
proc report nowd;
where region in ('Asia', 'Canada');
column region product sales inventory calcvar;
define region / group;
define product / group;
define sales / f=comma14.;
define inventory/ f=comma14.;
define calcvar / computed;
compute calcvar;
calcvar = sales.sum / inventory.sum;
if product = 'Boot' then
call define(_col_,'format','percent9.2');
compute sales;
if product = 'Sandal' then
call define(_col_,'format','dollar14.');

Although this example is changing a format for a particular column, you could also apply the CALL DEFINE statement to a particular _ROW_ or a particular variable or to an absolute column name.

If you look in the PROC REPORT documentation or search, you should be able to find some examples and figure out how to do what you need to do.

Not applicable
Posts: 0

Re: One Variable with Different Formats

Posted in reply to Cynthia_sas

if you prefer or need, to use some reporting method other than proc report, one feature available in the SAS System might help. User-defined formats created with PROC FORMAT allow you to use different formats for different ranges of values. The statement is documented at . To take advantage of this you would probably need to "store" the percentages in a peculiar range of values (e.g.dividing percentage by 10e-5 pushes the number into very small ranges; rounding other results keeps them out of that range [any zero is always zero anyway]; and the user format picture statement supports a multiplier to return values in that "percentage small-range" back into their original range, and you can add a % symbol) .
Another direction that helps with reporting into excel, is the facility to push formulae into excel with the tagsets.excelXP destination in ODS. More information on that can be found in the ODS forum and in at the base SAS Focus area on "markup" speciality excelXP formulae examples at .
Could you just send the pecentage (and/or variance) as a formula ?

Good Luck

Ask a Question
Discussion stats
  • 3 replies
  • 3 in conversation