10-14-2014 01:42 AM
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.
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.
10-14-2014 03:56 AM
Select distinct Week
Select distinct Key
Merge back with original data - retain Value
10-14-2014 06:42 AM
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.
10-14-2014 07:12 AM
Then do select distinct product as well....or not? Perhaps you could try to describe what you want achieve by attaching a sample output?
10-14-2014 07:20 AM
It will retain for all the key-product combinations. I want to retain the key-product combination value only if key is missing
10-14-2014 09:08 AM
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.
10-14-2014 09:41 AM
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;
Message was edited by: xia keshan
10-15-2014 04:04 AM
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.
10-16-2014 08:07 AM
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) ?