topic Re: replacing a row value to previous by group and amount in New SAS User

replacing a row value to previous by group and amount

SD714 — Tue, 07 Apr 2020 14:25:48 GMT

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!

Re: replacing a row value to previous by group and amount

Kurt_Bremser — Tue, 07 Apr 2020 14:34:39 GMT

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).

Re: replacing a row value to previous by group and amount

SD714 — Tue, 07 Apr 2020 14:42:46 GMT

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

Re: replacing a row value to previous by group and amount

Kurt_Bremser — Tue, 07 Apr 2020 14:49:14 GMT

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

Re: replacing a row value to previous by group and amount

SD714 — Tue, 07 Apr 2020 14:53:50 GMT

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

Re: replacing a row value to previous by group and amount

Kurt_Bremser — Tue, 07 Apr 2020 15:08:27 GMT

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;

Re: replacing a row value to previous by group and amount

mkeintz — Tue, 07 Apr 2020 16:53:35 GMT

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

Re: replacing a row value to previous by group and amount

Ksharp — Wed, 08 Apr 2020 12:25:03 GMT

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;

Re: replacing a row value to previous by group and amount

SD714 — Thu, 09 Apr 2020 17:51:38 GMT

This worked as expected. Thank you so much!