The layout of the report is:
Last_name first_name city state zip bsm 2009_1Qtotal 2009_1Qshare 2009_2Qtotal 2009_2Q share ...2008_1Qtotal 2008_1Qshare
I.e rolling quarters from current month(quater) to 2008 1Q.
I use proc sql to come up with each quater total and share.
share is sum of "mydrug"mg/sum of total mg.
I'm wondering if there is anyway out to circumvent my long proc sql statements.
As a sample please see my code :
proc sql;
create table toppres as
select phy_name,City ,state,ZIP,ghtm,
sum(case
when ship_date >= intnx ('qtr',"&sysdate"D,-1) and ship_date < intnx ('month',intnx('qtr',"&sysdate"d,-1),3) then mg_dispensed end) as Q1total,
sum(case
when ship_date >= intnx ('qtr',"&sysdate"D,-1) and
ship_date < intnx ('month',intnx('qtr',"&sysdate"d,-1),3) and drug='NORDITROPIN' then mg_dispensed end) as Q1nordi
from allphysicians_gsm
group by phy_name,City ,state, drug,ghtm,ZIP
order by q1total desc;
quit;
so the 1Q share is q1nordi/q1total.
