topic Re: Retain to keep latest record in SAS Programming

Retain to keep latest record

Scott86 — Fri, 01 May 2020 02:19:40 GMT

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

Re: Retain to keep latest record

Reeza — Fri, 01 May 2020 03:19:16 GMT

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;

Re: Retain to keep latest record

RichardDeVen — Fri, 01 May 2020 03:23:04 GMT

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;

Re: Retain to keep latest record

Astounding — Fri, 01 May 2020 08:21:00 GMT

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;

Re: Retain to keep latest record

novinosrin — Fri, 01 May 2020 11:45:51 GMT

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.

Re: Retain to keep latest record

Quentin — Fri, 01 May 2020 13:14:57 GMT

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

Re: Retain to keep latest record

novinosrin — Fri, 01 May 2020 13:31:35 GMT

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

Re: Retain to keep latest record

FreelanceReinh — Fri, 01 May 2020 13:50:19 GMT

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

Re: Retain to keep latest record

Scott86 — Sun, 03 May 2020 20:50:12 GMT

Thanks guys this worked