turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- calculate percentage based on product

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-06-2011 03:55 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to sasg

07-07-2011 03:18 PM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to DBailey

07-07-2011 06:32 PM

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