DATA Step, Macro, Functions and more

Loop through year, quarter,month and column value

Reply
Contributor
Posts: 47

Loop through year, quarter,month and column value

[ Edited ]

I have a table having one column named: Primary_coverage as shown below, 

Primary_coverage
combined single limit
uninsured motorist
Medical
Bodily Injury

 

 

 I want to creat a macro to loop through Year, Quarter, Month and column value dynamically,so that my table value should look as shown below:  (2000Q1 to 2002Q4=36 months, 12 quarters, 3 years)

Earliest year will always begin at 2000Q1

yearquarterPrimary_coveragemonths
20001combined single limit36
20002combined single limit33
20003combined single limit30
20004combined single limit27
20011combined single limit24
20012combined single limit21
20013combined single limit18
20014combined single limit15
20021combined single limit12
20022combined single limit9
20023combined single limit6
20024combined single limit3
20001uninsured motorist36
20002uninsured motorist33
20003uninsured motorist30
20004uninsured motorist27
20011uninsured motorist24
20012uninsured motorist21
20013uninsured motorist18
20014uninsured motorist15
20021uninsured motorist12
20022uninsured motorist9
20023uninsured motorist6
20024uninsured motorist3
20001Medical36
20002Medical33
20003Medical30
20004Medical27
20011Medical24
20012Medical21
20013Medical18
20014Medical15
20021Medical12
20022Medical9
20023Medical6
20024Medical3
20001Bodily Injury36
20002Bodily Injury33
20003Bodily Injury30
20004Bodily Injury27
20011Bodily Injury24
20012Bodily Injury21
20013Bodily Injury18
20014Bodily Injury15
20021Bodily Injury12
20022Bodily Injury9
20023Bodily Injury6
20024Bodily Injury3

 

 BUT When I RUN the SAME PROGRAM ON 2003Q2   My table should look as shown below:        (2000Q1 to 2003Q2=42 months,14 Quarters, 3 years)

 

yearquarterPrimary_coveragemonths
20001combined single limit42
20002combined single limit39
20003combined single limit36
20004combined single limit33
20011combined single limit30
20012combined single limit27
20013combined single limit24
20014combined single limit21
20021combined single limit18
20022combined single limit15
20023combined single limit12
20024combined single limit9
20031combined single limit6
20032combined single limit3
20001uninsured motorist42
20002uninsured motorist39
20003uninsured motorist36
20004uninsured motorist33
20011uninsured motorist30
20012uninsured motorist27
20013uninsured motorist24
20014uninsured motorist21
20021uninsured motorist18
20022uninsured motorist15
20023uninsured motorist12
20024uninsured motorist9
20031uninsured motorist6
20032uninsured motorist3
20001Medical42
20002Medical39
20003Medical36
20004Medical33
20011Medical30
20012Medical27
20013Medical24
20014Medical21
20021Medical18
20022Medical15
20023Medical12
20024Medical9
20031Medical6
20032Medical3
20001Bodily Injury42
20002Bodily Injury39
20003Bodily Injury36
20004Bodily Injury33
20011Bodily Injury30
20012Bodily Injury27
20013Bodily Injury24
20014Bodily Injury21
20021Bodily Injury18
20022Bodily Injury15
20023Bodily Injury12
20024Bodily Injury9
20031Bodily Injury6
20032Bodily Injury3

 

Super User
Posts: 5,085

Re: Loop through year, quarter,month and column value

It looks like your last year may end mid-year.

 

Does your earliest year always begin with Q1, or could it begin midway through the year?

Contributor
Posts: 47

Re: Loop through year, quarter,month and column value

It will begin at 2000q1
Super User
Posts: 5,085

Re: Loop through year, quarter,month and column value

OK, this would probably do it:

 

%macro test (outdata=, end_year=, end_q=);

%local months;
%let months = %eval(12 * (&end_year - 1999));
%if &end_q=3 %then %let months = %eval(&months-3);
%else %if &end_q=2 %then %let months = %eval(&months-6);
%else %if &end_q=1 %then %let months = %eval(&months-9);

data &outdata;
length year quarter 8;
length primary_coverage $ 21;
length months 8;
do Primary_coverage='combined single limit', 'uninsured motorist', 'Medical', 'Bodily injury';
   months = &months;
   do year=2000 to &end_year;
      do quarter=1 to 4;
         if year < &end_year or quarter <= &end_q then output;
         months = months-3;
      end;
   end;
end;
run;

%mend test;

%test (outdata=_2003_q2, end_year=2003, end_q=2)

proc print;
run;

Contributor
Posts: 47

Loop Through Year, Quarter, Month and Column Value Dynamically

I have a table having one column named: Primary_coverage  (having 2observations) as shown below,

Primary_coverage
combined single limit
uninsured motorist

 

 I want to creat a macro to loop through Year, Quarter, Month and column value dynamically,so that my table value should look as shown below: (2000Q1 to 2002Q4=36 months, 12 quarters, 3 years) 

Earliest year will always begin at 2000Q1

yearquarterPrimary_coveragemonths
20001combined single limit3
20001combined single limit6
20001combined single limit9
20001combined single limit12
20001combined single limit15
20001combined single limit18
20001combined single limit21
20001combined single limit24
20001combined single limit27
20001combined single limit30
20001combined single limit33
20001combined single limit36
20002combined single limit3
20002combined single limit6
20002combined single limit9
20002combined single limit12
20002combined single limit15
20002combined single limit18
20002combined single limit21
20002combined single limit24
20002combined single limit27
20002combined single limit30
20002combined single limit33
20002combined single limit36
20003combined single limit3
20003combined single limit6
20003combined single limit9
20003combined single limit12
20003combined single limit15
20003combined single limit18
20003combined single limit21
20003combined single limit24
20003combined single limit27
20003combined single limit30
20003combined single limit33
20003combined single limit36
20004combined single limit3
20004combined single limit6
20004combined single limit9
20004combined single limit12
20004combined single limit15
20004combined single limit18
20004combined single limit21
20004combined single limit24
20004combined single limit27
20004combined single limit30
20004combined single limit33
20004combined single limit36
20011combined single limit3
20011combined single limit6
20011combined single limit9
20011combined single limit12
20011combined single limit15
20011combined single limit18
20011combined single limit21
20011combined single limit24
20011combined single limit27
20011combined single limit30
20011combined single limit33
20011combined single limit36
20012combined single limit3
20012combined single limit6
20012combined single limit9
20012combined single limit12
20012combined single limit15
20012combined single limit18
20012combined single limit21
20012combined single limit24
20012combined single limit27
20012combined single limit30
20012combined single limit33
20012combined single limit36
20013combined single limit3
20013combined single limit6
20013combined single limit9
20013combined single limit12
20013combined single limit15
20013combined single limit18
20013combined single limit21
20013combined single limit24
20013combined single limit27
20013combined single limit30
20013combined single limit33
20013combined single limit36
20014combined single limit3
20014combined single limit6
20014combined single limit9
20014combined single limit12
20014combined single limit15
20014combined single limit18
20014combined single limit21
20014combined single limit24
20014combined single limit27
20014combined single limit30
20014combined single limit33
20014combined single limit36
20021combined single limit3
20021combined single limit6
20021combined single limit9
20021combined single limit12
20021combined single limit15
20021combined single limit18
20021combined single limit21
20021combined single limit24
20021combined single limit27
20021combined single limit30
20021combined single limit33
20021combined single limit36
20022combined single limit3
20022combined single limit6
20022combined single limit9
20022combined single limit12
20022combined single limit15
20022combined single limit18
20022combined single limit21
20022combined single limit24
20022combined single limit27
20022combined single limit30
20022combined single limit33
20022combined single limit36
20023combined single limit3
20023combined single limit6
20023combined single limit9
20023combined single limit12
20023combined single limit15
20023combined single limit18
20023combined single limit21
20023combined single limit24
20023combined single limit27
20023combined single limit30
20023combined single limit33
20023combined single limit36
20024combined single limit3
20024combined single limit6
20024combined single limit9
20024combined single limit12
20024combined single limit15
20024combined single limit18
20024combined single limit21
20024combined single limit24
20024combined single limit27
20024combined single limit30
20024combined single limit33
20024combined single limit36
20001uninsured motorist3
20001uninsured motorist6
20001uninsured motorist9
20001uninsured motorist12
20001uninsured motorist15
20001uninsured motorist18
20001uninsured motorist21
20001uninsured motorist24
20001uninsured motorist27
20001uninsured motorist30
20001uninsured motorist33
20001uninsured motorist36
20002uninsured motorist3
20002uninsured motorist6
20002uninsured motorist9
20002uninsured motorist12
20002uninsured motorist15
20002uninsured motorist18
20002uninsured motorist21
20002uninsured motorist24
20002uninsured motorist27
20002uninsured motorist30
20002uninsured motorist33
20002uninsured motorist36
20003uninsured motorist3
20003uninsured motorist6
20003uninsured motorist9
20003uninsured motorist12
20003uninsured motorist15
20003uninsured motorist18
20003uninsured motorist21
20003uninsured motorist24
20003uninsured motorist27
20003uninsured motorist30
20003uninsured motorist33
20003uninsured motorist36
20004uninsured motorist3
20004uninsured motorist6
20004uninsured motorist9
20004uninsured motorist12
20004uninsured motorist15
20004uninsured motorist18
20004uninsured motorist21
20004uninsured motorist24
20004uninsured motorist27
20004uninsured motorist30
20004uninsured motorist33
20004uninsured motorist36
20011uninsured motorist3
20011uninsured motorist6
20011uninsured motorist9
20011uninsured motorist12
20011uninsured motorist15
20011uninsured motorist18
20011uninsured motorist21
20011uninsured motorist24
20011uninsured motorist27
20011uninsured motorist30
20011uninsured motorist33
20011uninsured motorist36
20012uninsured motorist3
20012uninsured motorist6
20012uninsured motorist9
20012uninsured motorist12
20012uninsured motorist15
20012uninsured motorist18
20012uninsured motorist21
20012uninsured motorist24
20012uninsured motorist27
20012uninsured motorist30
20012uninsured motorist33
20012uninsured motorist36
20013uninsured motorist3
20013uninsured motorist6
20013uninsured motorist9
20013uninsured motorist12
20013uninsured motorist15
20013uninsured motorist18
20013uninsured motorist21
20013uninsured motorist24
20013uninsured motorist27
20013uninsured motorist30
20013uninsured motorist33
20013uninsured motorist36
20014uninsured motorist3
20014uninsured motorist6
20014uninsured motorist9
20014uninsured motorist12
20014uninsured motorist15
20014uninsured motorist18
20014uninsured motorist21
20014uninsured motorist24
20014uninsured motorist27
20014uninsured motorist30
20014uninsured motorist33
20014uninsured motorist36
20021uninsured motorist3
20021uninsured motorist6
20021uninsured motorist9
20021uninsured motorist12
20021uninsured motorist15
20021uninsured motorist18
20021uninsured motorist21
20021uninsured motorist24
20021uninsured motorist27
20021uninsured motorist30
20021uninsured motorist33
20021uninsured motorist36
20022uninsured motorist3
20022uninsured motorist6
20022uninsured motorist9
20022uninsured motorist12
20022uninsured motorist15
20022uninsured motorist18
20022uninsured motorist21
20022uninsured motorist24
20022uninsured motorist27
20022uninsured motorist30
20022uninsured motorist33
20022uninsured motorist36
20023uninsured motorist3
20023uninsured motorist6
20023uninsured motorist9
20023uninsured motorist12
20023uninsured motorist15
20023uninsured motorist18
20023uninsured motorist21
20023uninsured motorist24
20023uninsured motorist27
20023uninsured motorist30
20023uninsured motorist33
20023uninsured motorist36
20024uninsured motorist3
20024uninsured motorist6
20024uninsured motorist9
20024uninsured motorist12
20024uninsured motorist15
20024uninsured motorist18
20024uninsured motorist21
20024uninsured motorist24
20024uninsured motorist27
20024uninsured motorist30
20024uninsured motorist33
20024uninsured motorist36

 

 BUT When I RUN the SAME PROGRAM ON 2003Q2 My table should look as shown below: (2000Q1 to 2003Q2=42 months,14 Quarters, 3 years)

yearquarterPrimary_coveragemonths
20001combined single limit3
20001combined single limit6
20001combined single limit9
20001combined single limit12
20001combined single limit15
20001combined single limit18
20001combined single limit21
20001combined single limit24
20001combined single limit27
20001combined single limit30
20001combined single limit33
20001combined single limit36
20001combined single limit39
20001combined single limit42
20002combined single limit3
20002combined single limit6
20002combined single limit9
20002combined single limit12
20002combined single limit15
20002combined single limit18
20002combined single limit21
20002combined single limit24
20002combined single limit27
20002combined single limit30
20002combined single limit33
20002combined single limit36
20002combined single limit39
20002combined single limit42
20003combined single limit3
20003combined single limit6
20003combined single limit9
20003combined single limit12
20003combined single limit15
20003combined single limit18
20003combined single limit21
20003combined single limit24
20003combined single limit27
20003combined single limit30
20003combined single limit33
20003combined single limit36
20003combined single limit39
20003combined single limit42
20004combined single limit3
20004combined single limit6
20004combined single limit9
20004combined single limit12
20004combined single limit15
20004combined single limit18
20004combined single limit21
20004combined single limit24
20004combined single limit27
20004combined single limit30
20004combined single limit33
20004combined single limit36
20004combined single limit39
20004combined single limit42
20011combined single limit3
20011combined single limit6
20011combined single limit9
20011combined single limit12
20011combined single limit15
20011combined single limit18
20011combined single limit21
20011combined single limit24
20011combined single limit27
20011combined single limit30
20011combined single limit33
20011combined single limit36
20011combined single limit39
20011combined single limit42
20012combined single limit3
20012combined single limit6
20012combined single limit9
20012combined single limit12
20012combined single limit15
20012combined single limit18
20012combined single limit21
20012combined single limit24
20012combined single limit27
20012combined single limit30
20012combined single limit33
20012combined single limit36
20012combined single limit39
20012combined single limit42
20013combined single limit3
20013combined single limit6
20013combined single limit9
20013combined single limit12
20013combined single limit15
20013combined single limit18
20013combined single limit21
20013combined single limit24
20013combined single limit27
20013combined single limit30
20013combined single limit33
20013combined single limit36
20013combined single limit39
20013combined single limit42
20014combined single limit3
20014combined single limit6
20014combined single limit9
20014combined single limit12
20014combined single limit15
20014combined single limit18
20014combined single limit21
20014combined single limit24
20014combined single limit27
20014combined single limit30
20014combined single limit33
20014combined single limit36
20014combined single limit39
20014combined single limit42
20021combined single limit3
20021combined single limit6
20021combined single limit9
20021combined single limit12
20021combined single limit15
20021combined single limit18
20021combined single limit21
20021combined single limit24
20021combined single limit27
20021combined single limit30
20021combined single limit33
20021combined single limit36
20021combined single limit39
20021combined single limit42
20022combined single limit3
20022combined single limit6
20022combined single limit9
20022combined single limit12
20022combined single limit15
20022combined single limit18
20022combined single limit21
20022combined single limit24
20022combined single limit27
20022combined single limit30
20022combined single limit33
20022combined single limit36
20022combined single limit39
20022combined single limit42
20023combined single limit3
20023combined single limit6
20023combined single limit9
20023combined single limit12
20023combined single limit15
20023combined single limit18
20023combined single limit21
20023combined single limit24
20023combined single limit27
20023combined single limit30
20023combined single limit33
20023combined single limit36
20023combined single limit39
20023combined single limit42
20024combined single limit3
20024combined single limit6
20024combined single limit9
20024combined single limit12
20024combined single limit15
20024combined single limit18
20024combined single limit21
20024combined single limit24
20024combined single limit27
20024combined single limit30
20024combined single limit33
20024combined single limit36
20024combined single limit39
20024combined single limit42
20031combined single limit3
20031combined single limit6
20031combined single limit9
20031combined single limit12
20031combined single limit15
20031combined single limit18
20031combined single limit21
20031combined single limit24
20031combined single limit27
20031combined single limit30
20031combined single limit33
20031combined single limit36
20031combined single limit39
20031combined single limit42
20032combined single limit3
20032combined single limit6
20032combined single limit9
20032combined single limit12
20032combined single limit15
20032combined single limit18
20032combined single limit21
20032combined single limit24
20032combined single limit27
20032combined single limit30
20032combined single limit33
20032combined single limit36
20032combined single limit39
20032combined single limit42
20001uninsured motorist3
20001uninsured motorist6
20001uninsured motorist9
20001uninsured motorist12
20001uninsured motorist15
20001uninsured motorist18
20001uninsured motorist21
20001uninsured motorist24
20001uninsured motorist27
20001uninsured motorist30
20001uninsured motorist33
20001uninsured motorist36
20001uninsured motorist39
20001uninsured motorist42
20002uninsured motorist3
20002uninsured motorist6
20002uninsured motorist9
20002uninsured motorist12
20002uninsured motorist15
20002uninsured motorist18
20002uninsured motorist21
20002uninsured motorist24
20002uninsured motorist27
20002uninsured motorist30
20002uninsured motorist33
20002uninsured motorist36
20002uninsured motorist39
20002uninsured motorist42
20003uninsured motorist3
20003uninsured motorist6
20003uninsured motorist9
20003uninsured motorist12
20003uninsured motorist15
20003uninsured motorist18
20003uninsured motorist21
20003uninsured motorist24
20003uninsured motorist27
20003uninsured motorist30
20003uninsured motorist33
20003uninsured motorist36
20003uninsured motorist39
20003uninsured motorist42
20004uninsured motorist3
20004uninsured motorist6
20004uninsured motorist9
20004uninsured motorist12
20004uninsured motorist15
20004uninsured motorist18
20004uninsured motorist21
20004uninsured motorist24
20004uninsured motorist27
20004uninsured motorist30
20004uninsured motorist33
20004uninsured motorist36
20004uninsured motorist39
20004uninsured motorist42
20011uninsured motorist3
20011uninsured motorist6
20011uninsured motorist9
20011uninsured motorist12
20011uninsured motorist15
20011uninsured motorist18
20011uninsured motorist21
20011uninsured motorist24
20011uninsured motorist27
20011uninsured motorist30
20011uninsured motorist33
20011uninsured motorist36
20011uninsured motorist39
20011uninsured motorist42
20012uninsured motorist3
20012uninsured motorist6
20012uninsured motorist9
20012uninsured motorist12
20012uninsured motorist15
20012uninsured motorist18
20012uninsured motorist21
20012uninsured motorist24
20012uninsured motorist27
20012uninsured motorist30
20012uninsured motorist33
20012uninsured motorist36
20012uninsured motorist39
20012uninsured motorist42
20013uninsured motorist3
20013uninsured motorist6
20013uninsured motorist9
20013uninsured motorist12
20013uninsured motorist15
20013uninsured motorist18
20013uninsured motorist21
20013uninsured motorist24
20013uninsured motorist27
20013uninsured motorist30
20013uninsured motorist33
20013uninsured motorist36
20013uninsured motorist39
20013uninsured motorist42
20014uninsured motorist3
20014uninsured motorist6
20014uninsured motorist9
20014uninsured motorist12
20014uninsured motorist15
20014uninsured motorist18
20014uninsured motorist21
20014uninsured motorist24
20014uninsured motorist27
20014uninsured motorist30
20014uninsured motorist33
20014uninsured motorist36
20014uninsured motorist39
20014uninsured motorist42
20021uninsured motorist3
20021uninsured motorist6
20021uninsured motorist9
20021uninsured motorist12
20021uninsured motorist15
20021uninsured motorist18
20021uninsured motorist21
20021uninsured motorist24
20021uninsured motorist27
20021uninsured motorist30
20021uninsured motorist33
20021uninsured motorist36
20021uninsured motorist39
20021uninsured motorist42
20022uninsured motorist3
20022uninsured motorist6
20022uninsured motorist9
20022uninsured motorist12
20022uninsured motorist15
20022uninsured motorist18
20022uninsured motorist21
20022uninsured motorist24
20022uninsured motorist27
20022uninsured motorist30
20022uninsured motorist33
20022uninsured motorist36
20022uninsured motorist39
20022uninsured motorist42
20023uninsured motorist3
20023uninsured motorist6
20023uninsured motorist9
20023uninsured motorist12
20023uninsured motorist15
20023uninsured motorist18
20023uninsured motorist21
20023uninsured motorist24
20023uninsured motorist27
20023uninsured motorist30
20023uninsured motorist33
20023uninsured motorist36
20023uninsured motorist39
20023uninsured motorist42
20024uninsured motorist3
20024uninsured motorist6
20024uninsured motorist9
20024uninsured motorist12
20024uninsured motorist15
20024uninsured motorist18
20024uninsured motorist21
20024uninsured motorist24
20024uninsured motorist27
20024uninsured motorist30
20024uninsured motorist33
20024uninsured motorist36
20024uninsured motorist39
20024uninsured motorist42
20031uninsured motorist3
20031uninsured motorist6
20031uninsured motorist9
20031uninsured motorist12
20031uninsured motorist15
20031uninsured motorist18
20031uninsured motorist21
20031uninsured motorist24
20031uninsured motorist27
20031uninsured motorist30
20031uninsured motorist33
20031uninsured motorist36
20031uninsured motorist39
20031uninsured motorist42
20032uninsured motorist3
20032uninsured motorist6
20032uninsured motorist9
20032uninsured motorist12
20032uninsured motorist15
20032uninsured motorist18
20032uninsured motorist21
20032uninsured motorist24
20032uninsured motorist27
20032uninsured motorist30
20032uninsured motorist33
20032uninsured motorist36
20032uninsured motorist39
20032uninsured motorist42
Super Contributor
Posts: 408

Re: Loop Through Year, Quarter, Month and Column Value Dynamically

This can be done without a macro:

 

data coverage;
    primary_coverage="combined single limit";
    output;
    primary_coverage="uninsured motorist";
    output;
run;

data want;
    length year quarter 8;
    set coverage;

    do year=2000 to 2002;
        do quarter=1 to 4;
            do months=3 to 36 by 3;
                output;
            end;
        end;
    end;
run;
Contributor
Posts: 47

Re: Loop Through Year, Quarter, Month and Column Value Dynamically

This code is not working for when i run it for 2003Q2 as its not a macro
Super User
Posts: 17,868

Re: Loop Through Year, Quarter, Month and Column Value Dynamically

You posted this in several places yesterday and received several solutions. Please don't repost the same question. 

 

Did neither of the proposed solutions work for you? 

Contributor
Posts: 47

Re: Loop Through Year, Quarter, Month and Column Value Dynamically

Its a entirely different logic ,
Super User
Posts: 17,868

Re: Loop Through Year, Quarter, Month and Column Value Dynamically


subrat1 wrote:
Its a entirely different logic ,

@Subrata Please explain the difference. There doesn't appear to be any difference besides a source for the primary coverage. 

Super User
Posts: 17,868

Re: Loop Through Year, Quarter, Month and Column Value Dynamically

Create a list of coverage types:

 

proc sql noprint;
Select distinct quote(primary_coverage) into :cov_list separated by ' ,'
From coverage_list;
Quit;

Then you can modify the do loop in the code above, either mine or Astoundings. 

 

Do coverage = &cov_list;
Super User
Posts: 17,868

Re: Loop through year, quarter,month and column value

Here's a full data step solution, that only requires the end date, assuming you always end at 2000.

Replace your categories in the coverage list.

 

%let end_date = 2003Q2;

data date_list;
	format end_date date9. coverage $20.;
	end_date=input("&end_date", yyq6.);
	n_months=intck('month', '01Jan2000'd, end_date)+3;



	do coverage='Primary', 'Secondary', 'Tertiary';
	date=end_date;
		do months=n_months to 3 by -3;
			year=year(date);
			quarter=qtr(date);
			date=intnx('month', date, -3, 'b');
			output;
		end;
	end;
	Keep year quarter months coverage;
run;
Ask a Question
Discussion stats
  • 11 replies
  • 1009 views
  • 3 likes
  • 4 in conversation