03-02-2017 08:33 AM
I have a dataset in the below format:
Year_month Peril Average BC
201501 BI 130
201502 PD 150
201503 AD 170
I want to create a report such that when i plot this months in excel on X axis, i get an option to set one month as base and rest figures should be divided by that base figure. I want an option to change the base month like first it should 201501, then 201502 , I have attached a sample data to make it more clear. request yoy to provide the reponse asap.
03-02-2017 08:58 AM
This is how you get the values for one month:
proc sort data=have; by peril year_month; run; %let comp_month=201501; data want; merge have have ( rename=(bc=bc_comp year_month=ym) where=(ym = "&comp_month") ) ; by peril; comp_&comp_month = bc / bc_comp; drop ym bc_comp; run;
How you expand that for a multitude of months depends on what you want to do with the data later on.
Usually it is not a good idea to have data (year/month) in structure (variable names).
03-02-2017 09:03 AM
I devised an SQL approach:
%let selectym=201501; proc sql; create table want as select h1.yearmonth, h1.peril, h1.bc, h2.bc as bc2, h1.bc/h2.bc as bc_ratio from have h1, (select yearmonth, peril, bc from have h3 where h3.yearmonth=&selectym) h2 where h1.peril=h2.peril and h2.yearmonth=&selectym; quit;