DATA Step, Macro, Functions and more

DO loop Query

Reply
New User
Posts: 1

DO loop Query

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.

 

Super User
Posts: 7,762

Re: DO loop Query

Posted in reply to RamandeepSingh

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 438

Re: DO loop Query

Posted in reply to RamandeepSingh

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.

Ask a Question
Discussion stats
  • 2 replies
  • 115 views
  • 1 like
  • 3 in conversation