-1

SAS Question: I have a table having one column named: Primary_coverage having 4 observation as shown below,

Primary_coverage combined single limit uninsured motorist Medical Bodily Injury

I want to create 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) NOTE: Earliest year will always begin at 2000Q1

year    quarter Primary_coverage    months
2000    1   combined single limit   36
2000    2   combined single limit   33
2000    3   combined single limit   30
2000    4   combined single limit   27
2001    1   combined single limit   24
2001    2   combined single limit   21
2001    3   combined single limit   18
2001    4   combined single limit   15
2002    1   combined single limit   12
2002    2   combined single limit   9
2002    3   combined single limit   6
2002    4   combined single limit   3
2000    1   uninsured motorist  36
2000    2   uninsured motorist  33
2000    3   uninsured motorist  30
2000    4   uninsured motorist  27
2001    1   uninsured motorist  24
2001    2   uninsured motorist  21
2001    3   uninsured motorist  18
2001    4   uninsured motorist  15
2002    1   uninsured motorist  12
2002    2   uninsured motorist  9
2002    3   uninsured motorist  6
2002    4   uninsured motorist  3
2000    1   Medical 36
2000    2   Medical 33
2000    3   Medical 30
2000    4   Medical 27
2001    1   Medical 24
2001    2   Medical 21
2001    3   Medical 18
2001    4   Medical 15
2002    1   Medical 12
2002    2   Medical 9
2002    3   Medical 6
2002    4   Medical 3
2000    1   Bodily Injury   36
2000    2   Bodily Injury   33
2000    3   Bodily Injury   30
2000    4   Bodily Injury   27
2001    1   Bodily Injury   24
2001    2   Bodily Injury   21
2001    3   Bodily Injury   18
2001    4   Bodily Injury   15
2002    1   Bodily Injury   12
2002    2   Bodily Injury   9
2002    3   Bodily Injury   6
2002    4   Bodily Injury   3

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)

year    quarter Primary_coverage    months
2000    1   combined single limit   42
2000    2   combined single limit   39
2000    3   combined single limit   36
2000    4   combined single limit   33
2001    1   combined single limit   30
2001    2   combined single limit   27
2001    3   combined single limit   24
2001    4   combined single limit   21
2002    1   combined single limit   18
2002    2   combined single limit   15
2002    3   combined single limit   12
2002    4   combined single limit   9
2003    1   combined single limit   6
2003    2   combined single limit   3
2000    1   uninsured motorist  42
2000    2   uninsured motorist  39
2000    3   uninsured motorist  36
2000    4   uninsured motorist  33
2001    1   uninsured motorist  30
2001    2   uninsured motorist  27
2001    3   uninsured motorist  24
2001    4   uninsured motorist  21
2002    1   uninsured motorist  18
2002    2   uninsured motorist  15
2002    3   uninsured motorist  12
2002    4   uninsured motorist  9
2003    1   uninsured motorist  6
2003    2   uninsured motorist  3
2000    1   Medical 42
2000    2   Medical 39
2000    3   Medical 36
2000    4   Medical 33
2001    1   Medical 30
2001    2   Medical 27
2001    3   Medical 24
2001    4   Medical 21
2002    1   Medical 18
2002    2   Medical 15
2002    3   Medical 12
2002    4   Medical 9
2003    1   Medical 6
2003    2   Medical 3
2000    1   Bodily Injury   42
2000    2   Bodily Injury   39
2000    3   Bodily Injury   36
2000    4   Bodily Injury   33
2001    1   Bodily Injury   30
2001    2   Bodily Injury   27
2001    3   Bodily Injury   24
2001    4   Bodily Injury   21
2002    1   Bodily Injury   18
2002    2   Bodily Injury   15
2002    3   Bodily Injury   12
2002    4   Bodily Injury   9
2003    1   Bodily Injury   6
2003    2   Bodily Injury   3
Chris J
  • 7,549
  • 2
  • 25
  • 25
Subrat Swain
  • 11
  • 1
  • 2
  • 1
    I suggest you edit the question so that only the minimum information required is shown. There seems to be a lot of information in that table that is not relevant to what you are asking, and people are unlikely to try and decipher it because of that. See here about creating a Minimal, Complete, and Verifiable example: http://stackoverflow.com/help/mcve – Robert Penridge Jul 20 '16 at 19:10

1 Answers1

1

Use a combination of date functions to achieve this. Use INTNX to increment/decrement the date and input() to convert the YYYYQQ to a SAS date.

Do Loops are straightforward.

%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;
Reeza
  • 20,510
  • 4
  • 21
  • 38