BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8
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%
3 REPLIES 3
Cynthia_sas
SAS Super FREQ
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
Quartz | Level 8
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
SAS Super FREQ
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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 624 views
  • 0 likes
  • 2 in conversation