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.
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;
Thanks DBailey.....It is so helpful....i'm able to do it.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.