Retain

[MartinLomborg]

Please help 🙂

Want to create a "New Date" column, see example below. Based on Date, and retain within ID&ID2, when marking is "Mark"

See row 4 and 7 in example, where New Date is retain, only until cobination of ID&ID2 changes

Row	ID	ID2	ID2_No	Marking	Date	New Date
1	429857919	1	1		13-jun-17	13-jun-17
2	429857919	2	1		13-jun-17	13-jun-17
3	429857919	2	2	Mark	10-jul-17	10-jul-17
4	429857919	2	3		07-jul-17	10-jul-17
5	432852631	2	1		28-aug-17	28-aug-17
6	432852631	2	2	Mark	05-sep-17	05-sep-17
7	432852631	2	3		08-sep-17	05-sep-17
8	432852631	1	1		15-sep-17	15-sep-17
9	432852631	2	1		08-nov-17	08-nov-17
10	432852631	2	2		10-nov-17	10-nov-17

[Reeza]

What have you tried so far? RETAIN is the correct approach.

---

@MartinLomborg wrote:

Please help 🙂

Want to create a "New Date" column, see example below. Based on Date, and retain within ID&ID2, when marking is "Mark"

See row 4 and 7 in example, where New Date is retain, only until cobination of ID&ID2 changes

Row	ID	ID2	ID2_No	Marking	Date	New Date
1	429857919	1	1		13-jun-17	13-jun-17
2	429857919	2	1		13-jun-17	13-jun-17
3	429857919	2	2	Mark	10-jul-17	10-jul-17
4	429857919	2	3		07-jul-17	10-jul-17
5	432852631	2	1		28-aug-17	28-aug-17
6	432852631	2	2	Mark	05-sep-17	05-sep-17
7	432852631	2	3		08-sep-17	05-sep-17
8	432852631	1	1		15-sep-17	15-sep-17
9	432852631	2	1		08-nov-17	08-nov-17
10	432852631	2	2		10-nov-17	10-nov-17

---

[art297 · Accepted solution]

I think that the following does what you want:

data have;
  infile cards dlm='09'x;
  informat Date want_New_Date date11.;
  input Row ID ID2 ID2_No Marking $ Date want_New_Date;
  cards;
1	429857919	1	1	 	13-jun-17	13-jun-17
2	429857919	2	1	 	13-jun-17	13-jun-17
3	429857919	2	2	Mark	10-jul-17	10-jul-17
4	429857919	2	3	 	07-jul-17	10-jul-17
5	432852631	2	1	 	28-aug-17	28-aug-17
6	432852631	2	2	Mark	05-sep-17	05-sep-17
7	432852631	2	3	 	08-sep-17	05-sep-17
8	432852631	1	1	 	15-sep-17	15-sep-17
9	432852631	2	1	 	08-nov-17	08-nov-17
10	432852631	2	2	 	10-nov-17	10-nov-17
;
data want;
  set have;
  by ID ID2 notsorted;
  retain ret New_Date;
  if first.ID2 then ret=0;
  if Marking eq 'Mark' then do;
    ret=1;
    New_Date=Date;
  end;
  else if ret eq 0 then New_Date=Date;
run;

Art, CEO, AnalystFinder.com

[Astounding]

I think a simple version gets you there:

data want;

set have;

by id id2 notsorted;

if first.id2 or marking='Mark' then new_date = date;

retain new_date;

run;

[art297]

@Astounding: I, too, like parsimony. However, your code may have simplified too much as the 10th record gets assigned the wrong date.

Art, CEO, AnalystFinder.com

[Astounding]

@art297 you might be right.  I just couldn't see a reason why the value should change for the 10th observation.  Maybe the original poster will supply more of an explanation.

[MartinLomborg]

Thanks Guys, the 10th observation does not change in new date.

[Tom]

Data does not seem to be sorted by ID and ID2.  I added the NOTSORTED option to the BY statement to process it as is.

If you use a DOW loop then there should be no need for RETAIN.

data want ;
do until(last.id2);
  set have ;
  by id id2 notsorted;
  format new_date yymmdd10.;
  if not marked then new_date=date;
  if not missing(marking) then marked=1;
  output;
end;
run;