Retaining a key ,product combination if key is missing in a particular week

[munitech4u]

I have a dataset like this:

Week   Key  Product  Value

1         AB     TRN       2.4

1         AB     LJL        4.5

1         CD     LJL        3.4

2         AB     HTF       4.5

Now if say in 52 weeks, i have 100 keys. I want to look for those 100 keys in every week, and if its missing, I want to copy the all the last known data for that key to the missing week.

Here is a working solution where it retains the key, product combination:

But my situation is modified a little bit, I want to retain the combinations only if key is missing in a particular week, and not when key-product combination.

[LinusH]

Select distinct Week

Select distinct Key

Cartesian join

Merge back with original data - retain Value

[munitech4u]

this doesnt create the product combinations with keys. but sets them to missing. Retain will I believe retain only one last known product, I want to retain all the products for that key.

[LinusH]

Then do select distinct product as well....or not? Perhaps you could try to describe what you want achieve by attaching a sample output?

[munitech4u]

It will retain for all the key-product combinations. I want to retain the key-product combination value only if key is missing

[Ksharp]

What output do you need ?

[munitech4u]

that in the output dataset, apart from original, it should have the 100 keys in all weeks and if its missing in a particular week, retain its product and value combination from last known week.

[Ksharp]

Didn't @RW9  give you right answer ?

data have;
  week=1; key=1; product="AA"; val=234; output;
  week=1; key=2; product="BB"; val=45; output;
  week=2; key=1; product="AA"; val=98; output;
  week=3; key=1; product="AA"; val=23; output;
  week=3; key=2; product="BB"; val=12; output;
  week=4; key=2; product="BB"; val=5; output;
run;

proc sql;
create table temp as
 select a.*,b.product,b.val
  from (select * from (select distinct week from have),(select distinct key from have)) as a natural left join have as b 
   order by key,week ;
quit;
data want(drop=product val);
 set temp;
 by key;
 length  _product $ 40;
 retain  _product _val ;
 if first.key then call missing(_product,_val);
 if not missing(product) then _product=product;
 if not missing(val) then _val=val;
run;

Xia Keshan

Message was edited by: xia keshan

[munitech4u]

what happens in the case where key is not missing in a week, but key-product combination is.

[Ksharp]

I want to know if key was not missing in a week, why key-product combination would be missing ?

[munitech4u]

because not all keys will have all the products in a given week. So ideally for that week, i want it the missing key-product combination as zero and not retained one. Retaining should satisfy the first criteria of key itself missing.

[Ksharp]

I only considered key, not product . So if a key was missing in a week , and its product will have the same value with the last week(obs) ?