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.
Select distinct Week
Select distinct Key
Cartesian join
Merge back with original data - retain Value
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.
Then do select distinct product as well....or not? Perhaps you could try to describe what you want achieve by attaching a sample output?
It will retain for all the key-product combinations. I want to retain the key-product combination value only if key is missing
What output do you need ?
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.
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
what happens in the case where key is not missing in a week, but key-product combination is.
I want to know if key was not missing in a week, why key-product combination would be missing ?
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.
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) ?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.