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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.