BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MartinLomborg
Calcite | Level 5

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
1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

7 REPLIES 7
Reeza
Super User

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
Opal | Level 21

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
PROC Star

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
Opal | Level 21

@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
PROC Star

@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
Calcite | Level 5
Thanks Guys, the 10th observation does not change in new date.
Tom
Super User Tom
Super User

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

sas-innovate-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

Register now!

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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