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!
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;
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).
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 |
For tested code, supply data in usable form, in a data step with datalines, so I know exactly what your data looks like.
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;
@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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.