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?
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:
proc report data=sashelp.shoes 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;
calcvar = sales.sum / inventory.sum;
if product = 'Boot' then
if product = 'Sandal' then
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 support.sas.com, you should be able to find some examples and figure out how to do what you need to do.
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 http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a002473467.htm . 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 support.sas.com at the base SAS Focus area on "markup" speciality excelXP formulae examples at http://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html#formulas .
Could you just send the pecentage (and/or variance) as a formula ?