BookmarkSubscribeRSS Feed
munitech4u
Quartz | Level 8

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.

11 REPLIES 11
LinusH
Tourmaline | Level 20

Select distinct Week

Select distinct Key

Cartesian join

Merge back with original data - retain Value

Data never sleeps
munitech4u
Quartz | Level 8

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
Tourmaline | Level 20

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

Data never sleeps
munitech4u
Quartz | Level 8

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

munitech4u
Quartz | Level 8

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
Super User

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
Quartz | Level 8

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

Ksharp
Super User

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

munitech4u
Quartz | Level 8

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
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1178 views
  • 0 likes
  • 3 in conversation