topic array , dow loop in SAS Programming

array , dow loop

Moe_Issa — Tue, 16 Jun 2015 16:45:01 GMT

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;

Re: array , dow loop

PGStats — Wed, 17 Jun 2015 03:48:41 GMT

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;