topic Re: DO loop Query in SAS Programming

DO loop Query

RamandeepSingh — Thu, 02 Mar 2017 13:33:44 GMT

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.

Re: DO loop Query

Kurt_Bremser — Thu, 02 Mar 2017 13:58:14 GMT

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

Re: DO loop Query

jklaverstijn — Thu, 02 Mar 2017 14:03:56 GMT

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.