Solved
New Contributor
Posts: 2

# Retain

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

Accepted Solutions
Solution
‎11-23-2017 02:35 AM
PROC Star
Posts: 8,163

## Re: Retain

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

All Replies
Super User
Posts: 23,683

## Re: Retain

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

MartinLomborg wrote:

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

Solution
‎11-23-2017 02:35 AM
PROC Star
Posts: 8,163

## Re: Retain

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

Super User
Posts: 6,754

## Re: Retain

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;

PROC Star
Posts: 8,163

## Re: Retain

[ Edited ]

@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

Super User
Posts: 6,754

## Re: Retain

@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.

New Contributor
Posts: 2

## Re: Retain

Thanks Guys, the 10th observation does not change in new date.
Super User
Posts: 8,075

## Re: Retain

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;``````

☑ This topic is solved.

Discussion stats
• 7 replies
• 291 views
• 0 likes
• 5 in conversation