topic Re: Retain in SAS Procedures

Retain

MartinLomborg — Wed, 22 Nov 2017 16:35:33 GMT

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

Re: Retain

Reeza — Wed, 22 Nov 2017 16:54:22 GMT

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

Re: Retain

art297 — Wed, 22 Nov 2017 17:05:23 GMT

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

Re: Retain

Astounding — Wed, 22 Nov 2017 19:31:31 GMT

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;

Re: Retain

art297 — Wed, 22 Nov 2017 20:32:12 GMT

@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

Re: Retain

Astounding — Wed, 22 Nov 2017 20:47:31 GMT

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

Re: Retain

Tom — Thu, 23 Nov 2017 00:48:01 GMT

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;

Re: Retain

MartinLomborg — Thu, 23 Nov 2017 07:03:00 GMT

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