BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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?
3 REPLIES 3
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Cynthia_sas
SAS Super FREQ
Hi:
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:

[pre]
ods listing;
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;
compute calcvar;
calcvar = sales.sum / inventory.sum;
if product = 'Boot' then
call define(_col_,'format','percent9.2');
endcomp;
compute sales;
if product = 'Sandal' then
call define(_col_,'format','dollar14.');
endcomp;
run;
[/pre]

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.

cynthia
deleted_user
Not applicable
Virginia

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 ?

Good Luck

PeterC

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 697 views
  • 0 likes
  • 3 in conversation