- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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