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.
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).
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;
Regards,
- Jan.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.