replacing a row value to previous by group and amount

[SD714]

Hello,

Is there a way i could replace a row value to its previous row by each group. Below is the before and after dataset. Product for each type - C needs to be changed as type- L for each customer when the ID is same it has the highest amount.

Before:

Customer	ID	Type	Product	Amount
1	12432	L	A	234
1	12432	C	B	-
2	23213	L	C	23,212
2	23145	L	D	335
2	23145	C	E	-
3	21311	L	F	323
3	21324	L	G	2,344
3	21324	L	H	34
3	21324	C	I	-

After:

Customer	LINK_ID	Type	Product	Amount
1	12432	L	A	234
1	12432	C	A	-
2	23213	L	C	23,212
2	23145	L	D	335
2	23145	C	D	-
3	21311	L	F	323
3	21324	L	G	2,344
3	21324	L	H	34
3	21324	C	G	-

Thank you!

[Kurt_Bremser]

Use a retained variable:

data want;
set have;
retain _product;
if amount ne . then _product = product;
if amount = . and lag(customer) = customer then product = _product;
run;

Untested; for tested code, supply data in usable form (data step with datalines).

[SD714]

Thank you for your reply. It is still giving me the same product for type- C.

Obs Customer LINK_ID Type Product Amount _product123456789

1	12432	L	A	23	A
1	12432	C	B	0	B
2	23213	L	C	234	C
2	23145	L	D	25	D
2	23145	C	E	0	E
3	21311	L	F	34	F
3	21324	L	G	45	G
3	21324	L	H	35	H
3	21324	C	I	0	I

[Kurt_Bremser]

For tested code, supply data in usable form, in a data step with datalines, so I know exactly what your data looks like.

[SD714]

Here is the dataset.

data ccmrdata.chgdata;
input Customer LINK_ID Type $ Product $ Amount;

datalines;

1 12432 L A 23

1 12432 C B 0

2 23213 L C 234

2 23145 L D 25

2 23145 C E 0

3 21311 L F 34

3 21324 L G 45

3 21324 L H 35

3 21324 C I 0


;

run;

proc print data=ccmrdata.chgdata;

Obs Customer LINK_ID Type Product Amount
1 1 12432 L A 23
2 1 12432 C B 0
3 2 23213 L C 234
4 2 23145 L D 25
5 2 23145 C E 0
6 3 21311 L F 34
7 3 21324 L G 45
8 3 21324 L H 35
9 3 21324 C I 0

[Kurt_Bremser]

Then try this:

data have;
input Customer LINK_ID Type $ Product $ Amount;
datalines;
1 12432 L A 23
1 12432 C B 0
2 23213 L C 234
2 23145 L D 25
2 23145 C E 0
3 21311 L F 34
3 21324 L G 45
3 21324 L H 35
3 21324 C I 0
;

data want;
set have;
by customer;
retain _product _amount;
if first.customer
then do;
  _product = " ";
  _amount = .;
end;
if amount > _amount and type = 'L'
then do;
  _amount = amount;
  _product = product;
end;
if type = 'C' and _product ne ' ' then product = _product;
drop _:;
run;

[mkeintz]

---

@SD714 wrote:

Hello,

Is there a way i could replace a row value to its previous row by each group. Below is the before and after dataset. Product for each type - C needs to be changed as type- L for each customer when the ID is same it has the highest amount.

Before:

Customer	ID	Type	Product	Amount
1	12432	L	A	234
1	12432	C	B	-
2	23213	L	C	23,212
2	23145	L	D	335
2	23145	C	E	-
3	21311	L	F	323
3	21324	L	G	2,344
3	21324	L	H	34
3	21324	C	I	-

After:

Customer	LINK_ID	Type	Product	Amount
1	12432	L	A	234
1	12432	C	A	-
2	23213	L	C	23,212
2	23145	L	D	335
2	23145	C	D	-
3	21311	L	F	323
3	21324	L	G	2,344
3	21324	L	H	34
3	21324	C	G	-

Thank you!

---

I thought your rule was supposed to find, for each customer, the product code that is found in the type L record with maximum amount and assign it as product code for any records with type="C".  That's what you apparently do for customer 3  (the last record get a "G" from two records prior).  But for customer 2, you want the type='C' record to get product='D', which is NOT associated with the maximum type-L amount.

[Ksharp · Accepted solution]

data have;
input Customer LINK_ID Type $ Product $ Amount;
datalines;
1 12432 L A 23
1 12432 C B 0
2 23213 L C 234
2 23145 L D 25
2 23145 C E 0
3 21311 L F 34
3 21324 L G 45
3 21324 L H 35
3 21324 C I 0
;
proc sql;
create table temp as
select * from have 
 where Type='L'
  group by Customer, LINK_ID
   having amount=max(amount);
quit;
data want;
 if _n_=1 then do;
  if 0 then set temp;
  declare hash h(dataset:'temp');
  h.definekey('Customer', 'LINK_ID');
  h.definedata('Product');
  h.definedone();
 end;
set have;
if Type='C' then rc=h.find();
drop rc;
run;

[SD714]

This worked as expected. Thank you so much!