I'm stripping down a problem I'm having to a simplified version using a data step merge. I'm hoping it is a quick misunderstanding of that data step that someone can enlighten me on.
I have two datasets to merge. I've dummied some data below and changed the format as my data is proprietary.
data onshelf;
input product $ storeid $ mancode $ expdate packages;
informat expdate mmddyy10.;
format expdate mmddyy10.;
datalines;
12345 4985 882 06/19/2016 17
12345 4985 912 07/03/2016 6
12345 4985 125 07/17/2016 19
;;;
run;
data outlook;
input product $ storeid $ 'fcst_05/29/2016'n 'fcst_06/05/2016'n 'fcst_06/12/2016'n 'fcst_06/19/2016'n 'fcst_06/26/2016'n;
datalines;
12345 4985 12 4 5 2 2
;;;
run;
Thinking about bringing these datasets together
data together;
merge onshelf outlook;
by storeid product;
run;
The first record would be (generically)
product storeid mancode expdat packages forecasts
12345 4985 882 06/19/2016 17 12 4 5 2 2
What I want to do, is consume the inventory on shelf with the forecasted volume.
So, consume 12 of the packages with the first forecast, leaving 5 on the shelf. Then consume 4 more inventory units with the second forecast, leaving 1 on shelf. Then consume that last unit of inventory with the third forecast. This leaves inventory packages at 0 and 4 units in the third forecast. This record would then be output. e.g.
product storeid mancode expdat packages forecasts
12345 4985 882 06/19/2016 0 0 0 4 2 2
I can get to this pretty easily with an array and a little conditional logic to remove volume from packages and from the array'ed forecasts.
The problem is on the next record.
product storeid mancode expdate packages
12345 4985 912 07/03/2016 6
Notice this is the same product and storeid, but a different expiration date (different manufacturer line date for instance).
I want to use the forecast values left over from record 1 (0 0 4 2 2). However, I can't seem to get SAS to retain those values in the array. It is being reset to (12 4 5 2 2).
Is this type of activity possible? This is where I got my title from as it seems like I want to retain the values of the forecast as long as I'm in the same product, storeid combination.
Thanks in advance.
Can you post some more sample data and expected output? At least two cases so it's easier to test and output that matches your rules.
I think changing your data structure may make this easier, if you go long instead of wide.
You're right. It's not possible. Are you positive that your second data set doesn't contain the forecast variabnles?
Are you able to post the code that you used? Simplify it for the data that you illustrated, but call the forecast variables f1 through f5.
Hi @JoshB,
Maybe I've missed something, but I don't see your problem.
Variables that are read with a MERGE statement are automatically retained (see section "Redundancy" in the documentation of the RETAIN statement).
So, if you modify the forecast values based on the PACKAGES value of the first observation of ONSHELF, these modified forecast values will be retained and hence will be available for further modifications in the second observation and so on until the end of the BY group. They would not be "reset to (12 4 5 2 2)."
Simplified example: Reduce forecast values and PACKAGES by 1 in each observation (and avoid negative values). Of course, your algorithm would not be that trivial.
data outlook;
input product $ storeid $ f1-f5;
datalines;
12345 4985 12 4 5 2 2
;
data test;
merge onshelf outlook;
by storeid product;
array f f:;
do i=1 to dim(f);
f[i]=max(0,f[i]-1);
end;
packages=max(0,packages-1);
drop i;
run;
Result:
product storeid mancode expdate packages f1 f2 f3 f4 f5 12345 4985 882 06/19/2016 16 11 3 4 1 1 12345 4985 912 07/03/2016 5 10 2 3 0 0 12345 4985 125 07/17/2016 18 9 1 2 0 0
Thanks all for input. Turns out I had a couple extraneous output statements as I was debugging that led me down the "non-retained" path. It looked like the forecast measures were being re-initialized but I was actually just looking at duplicated output records. I was able to get to what I needed with my original logic.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: