CODEWITHSUNDEEP
sas
MD714
MD714

Reputation: 1

Replace a row value with previous by group in SAS

enter image description here

Is there a way I could replace a row value to its previous row by each group? Below is the before and after data set. 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

ObsCust 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

After

Cust 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!

Upvotes: 0

Views: 579

Answers (2)

Hesam Eivazy
Hesam Eivazy

Reputation: 61

if i understand correctly, you want to have product value for C Type be the product associated with the highest amount in L Types. If this is correct one possible way is to use the following. First the product with the highest amount for L-Type within each group of customers and IDs are calculated as follows: note that the original dataset is assumed to be named "example".

proc sql;
create table L_Type as
select cust, LINK_ID, product, amount
from example
where type = 'L' and amount = max(amount)
group by cust, LINK_ID 
;
quit;

then product calculated above is coded for c type in the original example.

proc sql;
select 
    e.cust
   , e.LINK_ID
   , e.type 
   , case when e.type = 'C' then b.product end as product
   , e.amount
from example e left join L_Type b
on e.cust = b.cust and e.LINK_ID = b.LINK_ID
;
quit;

Upvotes: 1

Richard
Richard

Reputation: 27508

So you have a couple processing tasks to do:

Have you considered all the edge cases ?

  • For a customer find the row(s) with the maximum amount.

    • Is one of them type L ?
    • No, do nothing

    • Yes, track the Product and LinkId as follows

      • Is there more than one 'maximal' row ?
      • No, track the Product & LinkId from the one row
      • Yes, Is there more than one Product in the rows ?
        • No, track the Product value
        • Is there more than one LinkId ?
          • No, track the LinkId
          • Yes, Which LinkIds?
          • Track all the different LinkIds
          • Track one of these: first, lowest, highest, last LinkId
        • Yes, now what ?
        • Log an error ?
        • Track one of the Product values because only one can be used, which one ?
          • first occurring ?
          • lowest value ?
          • highest value ?
          • last occurring ?

    • For the tracked LinkIds (there might not be any) apply the tracked Product to the rows that are type C (or perhaps type not L)

Upvotes: 0

Related Questions