Help using Base SAS procedures

Retain

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Retain

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

 

RowIDID2ID2_NoMarkingDateNew Date
142985791911 13-jun-1713-jun-17
242985791921 13-jun-1713-jun-17
342985791922Mark10-jul-1710-jul-17
442985791923 07-jul-1710-jul-17
543285263121 28-aug-1728-aug-17
643285263122Mark05-sep-1705-sep-17
743285263123 08-sep-1705-sep-17
843285263111 15-sep-1715-sep-17
943285263121 08-nov-1708-nov-17
1043285263122 10-nov-1710-nov-17

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

Re: Retain

Posted in reply to MartinLomborg

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

 

View solution in original post


All Replies
Super User
Posts: 23,683

Re: Retain

Posted in reply to MartinLomborg

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

 

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

Re: Retain

Posted in reply to MartinLomborg

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

Posted in reply to MartinLomborg

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 ]
Posted in reply to Astounding

@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

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

Re: Retain

Posted in reply to MartinLomborg

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;

image.png

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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