DO loop Query

[RamandeepSingh]

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.

[Kurt_Bremser]

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).

[jklaverstijn]

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.