Retaining value of variable to set another variable within a by-group

[eric_balke]

Hi all,

I have a problem I'm trying to solve and am in the middle of researching it but thought I'd try here as well.  Any help is greatly appreciated.

The data set I have is large but the operative fields are as follows:

order_no     pc_cat     pc_cat_no

1                    X               21

1                    Y               11

1                  NULL          22

1                 NULL           12

2                    X               21

2                 NULL            22

2                 NULL             3

What I need to do is reset the NULL values to X where pc_cat_no is equal to 22, and set the NULL values to Y where pc_cat_no is equal to 12 (because in the actual data pc_cat_no 12 is related to 11 and pc_cat_no 22 is related to 21), and leave NULL where pc_cat_no is anything else; but the hard part is I need to only search for and replace NULL's based on pc_cat_no within a given order_no.

I think it may be the retain function or some variant, but have been unable to crack the code thus far.

Thanks!

[Reeza]

Can you post sample data in the form of the data you have and the data you need.

I'm having a hard time seeing why if/then statements won't work.

[eric_balke]

Above is an image of the actual data.  The first problem which is depicted above is that there can be multiple Bucketing values for a given order.  I need to attribute other lines within the same order to either Bucket or neither depending on the value of prodcat.  The second, and bigger proglem I think, (sorry I wasn't clear in the first question) is that a given prodcat can apply to more than one "Bucketing" category, and the value I assign to Bucketing for a given prodcat depends on the value encountered on the order for Bucketing.  This problem is depicted in the following images.  In the first image, all lines where prodcat is 20

should be assigned a Bucketing value of CPAP, whereas in the second image all lines where prodcat is 20 should be assigned a Bucketing value of BiPAP.

Thanks again for your time!