topic Re: calculate percentage based on product in SAS Programming

calculate percentage based on product

sasg — Wed, 06 Jul 2011 19:55:01 GMT

Hi,

I have to create new coloums in dataset output (achieved rate and record volume) based on speciality pharmacy,
product and period in input dataset.

ex:we have dataset called input(input.csv)

from that we have to create 4 coloums in the dataset output.

if distinct Milestone_period for each product and pharmacy we have to create 5 new rows in
output dataset

    Approval Rate
    Days to Approval
    Fulfillment Rate
    Days to Ship
    Compliance

for calculating achieved rate for each row these are the formulas:

Days to Approval Sum of Days Achieved on records where Metric Field = AR
                    = ...................................................................
                        Count # of records where Metric Field = AR

Approval Rate (%) Count # of records where Metric Field = AR
= .........................................................................................................

Count # of records where Milestone Date = Referral

Days to Fulfillment Sum of Days Achieved on records where Metric Field = FR
=....................................................................................................
Count # of records where Metric Field = FR

Fulfillment Rate (%) Count # of records where Metric Field = FR
=........................................................
Count # of records where Milestone Date = Approval

Compliance Sum of Days of Therapy on records where Metric Field = COMP
=...........................................................
Sum of Days Achieved on records where Metric Field = COMP

Record count we based on speciality pharmacy,product,and period.I'm able to do it till Milestone period..after that i got struct with these calculations...
somebody pls guide me how to proceed with this one.

Thanks,
sasg.

Re: calculate percentage based on product

DBailey — Thu, 07 Jul 2011 19:18:18 GMT

This should get you started. I couldn't match some of your results given the formulas listed. Also, having column names with spaces is generally a bad idea.

proc sql;
create table work.summary as
select
 specialty_pharmacy,
 product,
 'Milestone Period'n,
 sum(metric='AR')/sum(Milestone_Type='Referral') as Approval_Rate format=percent7.2,
 sum(case when metric='AR' then 'Days Achieved'n else 0 end) / sum(metric='AR') as Days_To_Approval format=comma5.2,
 sum(metric='FR')/sum(Milestone_Type='Referral') as Fulfillment_Rate format=percent7.2,
 sum(metric='FR') as Days_To_Fullfilment,
 sum(case when metric='COMP' then days_of_therapy else 0 end) / sum(case when metric='COMP' then 'Days Achieved'n else 0 end)
  as Compliance format=comma5.2,
 count(*) as Records
from work.input
group by 
 specialty_pharmacy,
 product,
 'Milestone Period'n
;
quit;

Re: calculate percentage based on product

sasg — Thu, 07 Jul 2011 22:32:54 GMT

Thanks DBailey.....It is so helpful....i'm able to do it.