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 |
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
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
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
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;
@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
@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.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.