Retain to keep latest record

[Scott86]

Hi everyone, I am looking to use the retain statement to keep the latest code for each customer. I have come up with this code below but it is not working and I am getting stuck. my code is below:

data fix_code;
set lkup2;
retain r_code cust;
by customer_id;
if cust ne customer_id then do;
if not missing(code) then r_code = code;
else code = r_code;
end;
r_code = code;
cust = customer_id;
run;

I have attached a excel sheet with what I have and what I am wanting to achieve.

Thanks

[Reeza]

Multistep approach? Keep only last record and merge in? Retain carries records forward (down the column), in this case though your records are 'ahead' so you're trying to carry records backwards (up the column). 
This is a simple approach though there's like a hash solution @novinosrin 

data temp;
set have;
by customer_ID;
if last.customer_ID;
run;

data want;
merge have temp (rename= (code = last_code));
by customer_ID;
run;

[FreelanceReinh]

---

@Reeza wrote:

This is a simple approach though there's like a hash solution 

---

Sure, here's one of them:

data want;
if _n_=1 then do;
  dcl hash h(dataset:'have(where=(code>.z))', duplicate:'r');
  h.definekey('customer_id');
  h.definedata('code');
  h.definedone();
end;
set have;
_n_=h.find();
run;

[RichardDeVen]

You can use DOW loop processing to capture the final code, and a second loop to apply it.

data want;
  do _n_ = 1 by 1 until (last.customer_id);  /* repurpose _n_ */
    set have;
    by customer_id;
    last_code = code;   /* capture code */
  end;
  * at end of above loop last capture is last code in group;

  * use repurposed _n_ to iterate over the rows of the group;
  do _n_ = 1 to _n_;
    set have;                * second read buffer;
    code = last_code;  * apply captured value, overwriting original;
    OUTPUT;               * output one row per row of original data;
  end;
run;

[Astounding]

The idea is sound but needs a little bit of clean-up:

data want;
  do until (last.customer_id);
    set have;
    by customer_id;
    if not missing(code) then last_code = code; 
  end;
  * at end of above loop last capture is last non-missing code in group;

  do until (last.customer_id);
    set have;                * second read buffer;
    by customer_id;
    code = last_code;  * apply captured value, overwriting original;
    OUTPUT;               * output one row per row of original data;
  end;
run;

[novinosrin · Accepted solution]

Good morning everyone, My approach would be is to interleave and output the IN=b 🙂

data have;
input Customer_id	Code;
cards;
1	.
1	23
2	.
2	1666
2	4356
3	.
3	345
4	.
4	24
;

data want;
 set have(in=a) have(in=b);
 by customer_id;
 retain _code;
 if a then _code=code;
 if b;
 code=_code;
 drop _code;
run;
proc print noobs;run;

Customer_id	Code
1	23
1	23
2	4356
2	4356
2	4356
3	345
3	345
4	24
4	24

Sorry @Reeza, I dozed off last night after the proc sql fun and just noticed the mention. Thank you. And now in the AM dancing with inherited spaghetti code. 

[Quentin]

Really cool approach @novinosrin .  I'm so used to the DOW-loop approach, would never have thought of interleaving.

[novinosrin]

Thank you Sir @Quentin  That's the reason my professors at DePaul University, Chicago where I graduated had regarded me among the many students who will earnestly "listen and pay attention" to elders whom I value. (smiles)  🙂  🙂 Proud of it!

With the above being said, I owe you SAS veterans my sincere gratitude in getting me up-to speed with all the necessary instructions and guidance when I needed the most. I have nicknames for each one of you that my friends and I had a laugh with our fun SAS community stories at the Exchequer pub right opposite to the lab. Hahaha.  I miss those days and Chicago.  Have a good day

[Scott86]

Thanks guys this worked