Untested but this should get you started. You'll end up with a data set, step 3 that you can then decide how to impute the missing data.
Note that you can end up with duplicate matches so you'll need to decide how to filter/limit when you have multiple matches. You may want to add a key to your 'have' table to uniquely identify the rows.
DATA HAVE;
INFILE CARDS DLM=',' TRUNCOVER;
LENGTH PRODUCE MANUFACTURER BRAND $20;
INPUT Produce $ Manufacturer $ Brand $ Amount;
CARDS;
Beans, CLICxxx, Clicxxxx, 0
Kidneys, Pantry, Pantry Shelf, 0
Brussel Sprouts, Limson, Limson, 0
Cabbage, Bear, Bear Creek, 0
Chicken, Grannys, Grannys Poultry Cooperative, 0
Milk, Global, Global Direct, 0
Beans, Ital, Ital-Pasta, 4
Kidneys, Primo, Primo, 5
Brussel Sprouts, Alasko, Alasko, 6
Cabbage, Keybrand, Laurie's, 7
Chicken, Galco, Flamingo, 2
Chicken, Maple, , 3
Chicken, Maple, Kitchen Essential, 6
Milk, Agropur, Sealtest, 5
Milk, Saputo, Neilson, 6
;;;;
*MERGE IN BY MANUFACTURER AND BRAND;
proc sql;
create table step1 as
select t1.*, t2.amount as amount1
from have as t1
left join have as t2
on t1.produce = t2.produce
and t1.manufacturer=t2.manufacturer
and t1.brand=t2.brand;
quit;
*MERGE IN BY BRAND;
proc sql;
create table step2 as
select t1.*, t2.amount as amount2
from step1 as t1
left join have as t2
on t1.produce = t2.produce
and t1.brand=t2.brand;
quit;
*MERGE IN BY Manufacturer;
proc sql;
create table step3 as
select t1.*, t2.amount as amount3
from step2 as t1
left join have as t2
on t1.produce = t2.produce
and t1.manufacturer=t2.manufacturer;
quit;
/*insert data step to fill in logic here*/
data want;
set step3;
if amount = 0 the coalesce(amount1, amount2, amount3);
run;
@Gladis6680 wrote:
Thank you, is it possible for you to help me with a sample code. I think it will be easier for me to understand
