DATA Step, Macro, Functions and more

calculate percentage based on product

Reply
Contributor
Posts: 42

calculate percentage based on product

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.

Attachment
Super Contributor
Posts: 578

Re: calculate percentage based on product

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;

Contributor
Posts: 42

Re: calculate percentage based on product

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

Ask a Question
Discussion stats
  • 2 replies
  • 214 views
  • 3 likes
  • 2 in conversation