topic Re: Retaining a key ,product combination if key is missing in a particular week in SAS Procedures

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

munitech4u — Tue, 14 Oct 2014 05:42:41 GMT

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.

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

LinusH — Tue, 14 Oct 2014 07:56:09 GMT

Select distinct Week

Select distinct Key

Cartesian join

Merge back with original data - retain Value

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

munitech4u — Tue, 14 Oct 2014 10:42:45 GMT

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.

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

LinusH — Tue, 14 Oct 2014 11:12:12 GMT

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

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

munitech4u — Tue, 14 Oct 2014 11:20:21 GMT

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

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

Ksharp — Tue, 14 Oct 2014 12:45:20 GMT

What output do you need ?

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

munitech4u — Tue, 14 Oct 2014 13:08:00 GMT

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.

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

Ksharp — Tue, 14 Oct 2014 13:41:59 GMT

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

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

munitech4u — Tue, 14 Oct 2014 17:20:04 GMT

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

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

Ksharp — Wed, 15 Oct 2014 07:34:26 GMT

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

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

munitech4u — Wed, 15 Oct 2014 08:04:15 GMT

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.

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

Ksharp — Thu, 16 Oct 2014 12:07:37 GMT

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) ?