calculate percentage based on product

[sasg]

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.

[DBailey]

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;

[sasg]

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