## array , dow loop

Solved
Occasional Contributor
Posts: 12

# array , dow loop

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;

Accepted Solutions
Solution
‎06-16-2015 11:48 PM
Posts: 5,541

## Re: array , dow loop

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

PG

All Replies
Solution
‎06-16-2015 11:48 PM
Posts: 5,541

## Re: array , dow loop

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

PG
🔒 This topic is solved and locked.