topic Re: table 1 need to be as table 2 in New SAS User

table 1 need to be as table 2

aanan1417 — Sun, 25 Sep 2022 05:16:48 GMT

table 1

policy start_date end_date total_premium

p1 jan-19 jan-20 120

p2 jan-19 oct-19 100

p3 jan-19 may-19 50

table2

policy start_date end_date premium

p1 jan-19 feb-19 10

p1 feb-19 mar-19 10

p1 mar-19 apr-19 10

p1 apr-19 may-19 10

p1 may-19 jun-19 10

p1 jun-19 jul-19 10

p1 jul-19 aug-19 10

p1 aug-19 sep-19 10

p1 sep-19 oct-19 10

p1 oct-19 nov-19 10

p1 nov-19 dec-19 10

Re: table 1 need to be as table 2

Tom — Sun, 25 Sep 2022 05:45:08 GMT

Is this like Jeopardy? Are we supposed to supply the question that leads to your given answer?

Looks like you want to count the number of months and divide the premium by that number.

data have;
  input policy $ (start_date end_date) (:anydtdte.) total_premium ;
  format start_date end_date yymmdd10.;
cards;
p1        jan-19        jan-20        120
p2        jan-19        oct-19         100
p3        jan-19        may-19        50
;

data want;
  set have;
  months=intck('month',start_date,end_date);
  premium=total_premium/months;
  do month=1 to months;
    date=intnx('month',start_date,month-1,'b');
    output;
  end;
  format date yymmdd10.;
run;

                                              total_
Obs    policy    start_date      end_date    premium    months    premium    month          date

  1      p1      2019-01-01    2020-01-01      120        12      10.0000       1     2019-01-01
  2      p1      2019-01-01    2020-01-01      120        12      10.0000       2     2019-02-01
  3      p1      2019-01-01    2020-01-01      120        12      10.0000       3     2019-03-01
  4      p1      2019-01-01    2020-01-01      120        12      10.0000       4     2019-04-01
  5      p1      2019-01-01    2020-01-01      120        12      10.0000       5     2019-05-01
  6      p1      2019-01-01    2020-01-01      120        12      10.0000       6     2019-06-01
  7      p1      2019-01-01    2020-01-01      120        12      10.0000       7     2019-07-01
  8      p1      2019-01-01    2020-01-01      120        12      10.0000       8     2019-08-01
  9      p1      2019-01-01    2020-01-01      120        12      10.0000       9     2019-09-01
 10      p1      2019-01-01    2020-01-01      120        12      10.0000      10     2019-10-01
 11      p1      2019-01-01    2020-01-01      120        12      10.0000      11     2019-11-01
 12      p1      2019-01-01    2020-01-01      120        12      10.0000      12     2019-12-01
 13      p2      2019-01-01    2019-10-01      100         9      11.1111       1     2019-01-01
 14      p2      2019-01-01    2019-10-01      100         9      11.1111       2     2019-02-01
 15      p2      2019-01-01    2019-10-01      100         9      11.1111       3     2019-03-01
 16      p2      2019-01-01    2019-10-01      100         9      11.1111       4     2019-04-01
 17      p2      2019-01-01    2019-10-01      100         9      11.1111       5     2019-05-01
 18      p2      2019-01-01    2019-10-01      100         9      11.1111       6     2019-06-01
 19      p2      2019-01-01    2019-10-01      100         9      11.1111       7     2019-07-01
 20      p2      2019-01-01    2019-10-01      100         9      11.1111       8     2019-08-01
 21      p2      2019-01-01    2019-10-01      100         9      11.1111       9     2019-09-01
 22      p3      2019-01-01    2019-05-01       50         4      12.5000       1     2019-01-01
 23      p3      2019-01-01    2019-05-01       50         4      12.5000       2     2019-02-01
 24      p3      2019-01-01    2019-05-01       50         4      12.5000       3     2019-03-01
 25      p3      2019-01-01    2019-05-01       50         4      12.5000       4     2019-04-01

Re: table 1 need to be as table 2

andreas_lds — Mon, 26 Sep 2022 10:13:53 GMT

Please explain the logic you want applied and post the data in usable form. Is the second table complete? Should the first obs of table be processed, only?