summation

[SASPhile]

The dataset has phy_name,payer_name and mg_dispensed.
How to get in a single step or proc sql.
1.sum(mg_dispensed) at phy_name and payer_name
2.percentage of mg_dispensed which is sum(mg_dispensed) at phy_name and payer_name/ sum(mg_dispensed) at phy_name.

for instance:

FREEDMAN BCBS 100
FREEDMAN AETNA 200
FREEDAMN OTHER 200


FREEDMAN BCBS 100 20%
FREEDMAN AETNA 200 40%
FREEDMAN OTHER 200 40%

[Cynthia_sas]

Hi:
You could use either PROC REPORT or PROC TABULATE for this report (if the final output is a REPORT). It really depends on what you want -- whether you want an output REPORT or an output DATASET. There are pros and cons to either approach. If you use PROC REPORT, you'd have to calculate the percent based on physician using BY group processing, whereas with PROC TABULATE, you could use a custom denominator.

There's probably a DATA step or PROC SQL approach, but for either of those, you'd still need to find the total for each physician in order to generate the percent for each physician/payer combination.

cynthia

[SASPhile]

Cynthia,
I used two proc summary and then joined them to get what I need.I thought if there is any way where we could accomplish this in single step.
I need output report.
Thanks,

[Cynthia_sas]

My tendency would be to use PROC TABULATE, then. But you could also use PROC REPORT. The difference is that if you used TABULATE, you'd either have to use BY groups or a custom denominator and if you use REPORT, you'd have to use BY groups or a custom COMPUTE block. You really have several choices for how you'd approach this.

For example if you use TABULATE -without- a BY statement, then your TABLE statement might look like this:
[pre]
table phyname*(payer all),
mg_disp=' '*(sum*f=comma6. pctsum*f=pct.);
[/pre]

however, if you used TABULATE -with- a BY statement, then your TABLE statement might look like this:
[pre]
by phyname;
. . . more code . . .
table phyname*(payer all),
mg_disp=' '*(sum*f=comma6. pctsum*f=pct.);
[/pre]

Since every BY group is treated as a whole entity, there is no need for a custom denominator for PCTSUM when you use BY group processing.

The only other thing is that if you want a percent sign in the calculated percent, you'd have to use a custom PICTURE format, too (because TABULATE does an automatic multiply by 100 and the regular PERCENTw.d format also does a multiply by 100 -- so you can't use the PERCENT format with TABULATE):
[pre]
proc format;
picture pct low-high='009.99%';
run;
[/pre]

cynthia