DATA Step, Macro, Functions and more

DO loop Query

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: 10,574

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;

%let comp_month=201501;

data want;
  have (
    rename=(bc=bc_comp year_month=ym)
    where=(ym = "&comp_month")
by peril;
comp_&comp_month = bc / bc_comp;
drop ym bc_comp;

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
How to convert datasets to data steps
How to post code
Valued Guide
Posts: 540

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;


- Jan.

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