array , dow loop

[Moe_Issa]

Hi

Below is an example of a data I have and what im trying to achieve,

proc format ;

value formula

             low - 15 =0

            16-40 =1

           41-70=2

         71-100=3

         101-140=4

         141-175=5

       176-225=6

       226-

       301-high=8

;

       run;

data have;

input ID Email $ type $ edate mmddyy10. ;

format edate mmddyy10.;

datalines;

1 1 rdate 05/20/2015

1 2 rdate 05/21/2015

1 3 Sdate 10/05/2015

1 4 Sdate 11/09/2015

1 5 Sdate 12/11/2015

2 1 rdate 06/21/2015

2 2 rdate 07/05/2015

2 3 rdate 07/09/2015

2 4 Sdate 06/21/2016

2 5 Sdate 07/05/2016

2 6 Sdate 07/09/2016

;

run;

Im trying to subtract the last rdate  ( which is email 2 in this case) from first sdate ( email 3 in this case) . the number of days will translated to number of rows inserted ( refer to proc format )  . example if the number of days 130 . so its  130/4+1.  then insert 4 records equally distributed .

below is the final desire output .

any ideas how to approach that ?

;

data have;

input ID Email $ type $ edate mmddyy10. ;

format edate mmddyy10.;

datalines;

1 1 rdate 05/20/2015

1 2 rdate 05/21/2015

1 3 Formula 06/26/2015

1 4 Formula 08/02/2015

1 5 Formula 09/07/2015

1 6 Formula 10/13/2015

1 7 Sdate 11/18/2015

1 8 Sdate 11/30/2015

1 9 Sdate 12/11/2015

2 1 rdate 06/21/2015

2 2 rdate 07/05/2015

2 3 rdate 07/31/2015

2 4 Formula 08/21/2015

2 5 Formula 09/11/2015

2 6 Sdate 10/01/2016

2 7 Sdate 11/10/2015

2 8 Sdate 01/09/2016

;

run;

[PGStats · Accepted solution]

Here is a solution that doesn't involve a DOW loop:

proc format ;

value formula

low-15=0

16-40=1

41-70=2

71-100=3

101-140=4

141-175=5

176-225=6

226-300=7

301-high=8

;

run;

data have;

input ID Email $ type $ edate mmddyy10. ;

format edate mmddyy10.;

datalines;

1 1 rdate 05/20/2015

1 2 rdate 05/21/2015

1 3 Sdate 10/05/2015

1 4 Sdate 11/09/2015

1 5 Sdate 12/11/2015

2 1 rdate 06/21/2015

2 2 rdate 07/05/2015

2 3 rdate 07/09/2015

2 4 Sdate 06/21/2016

2 5 Sdate 07/05/2016

2 6 Sdate 07/09/2016

;

data want(keep=id newEmail type edate rename=newEmail=Email);

retain lastRdate;

set have; by id type notsorted;

if first.id then do;

    call missing(lastrdate,firstSdate);

    newEmail = 0;

    end;

if type="rdate" and last.type then lastRdate = edate;

if type="Sdate" and first.type and not missing(lastRdate) then do;

    firstSdate = edate;

    nbDates = input(put(intck("DAY",lastRdate,firstSdate),formula.),best.);

    dayStep = ceil(intck("DAY",lastRdate,firstSdate) / (nbDates+1));

    type = "Formula";

    do i = 1 to nbDates;

        edate = intnx('DAY', lastRdate, i*dayStep);

        newEmail + 1;

        output;

        end;

    type = "Sdate";

    edate = firstSdate;

    end;

newEmail + 1;

output;

run;

PG