Make the qty equal the spreading value

[hcbn]

Hello

If the value is equal to 12 in the qty column then I need to divide the qty column to the MinQ column. The result will be spreading in the desired output until the total qty for each dest id between the March 3 and March 12 is equal the sum of If the desired value.  If the qty is not equal 12, then I will keep the same value in the desired output. 

For example, dest id is 13827 and qty is 12 on March 8 and the desired output will start from March 8 as 4 pcs and keep continue until March 10 as 4 pcs so the total of the desired value  12 

The other example, if 12 in the qty column comes at the end of the dest id then the output has to be spread out earlier than March 12

For example, Dest id is 15007. There are two 12 inputs in different place of the qty column and the one is in the middle and the other one is at the end of the column so the 12 which is at the end of the column should start earlier March 10 because they have to refer 15007 Dest id. Thank you for your help

Input
dest_id	item	ScheduledArrivalDate	qty	MinQ
13827	400003	03Mar2019	0	3
13827	400003	04Mar2019	0	3
13827	400003	05Mar2019	0	3
13827	400003	06Mar2019	0	3
13827	400003	07Mar2019	0	3
13827	400003	08Mar2019	12	3
13827	400003	09Mar2019	0	3
13827	400003	10Mar2019	0	3
13827	400003	11Mar2019	0	3
13827	400003	12Mar2019	0	3
15007	400003	03Mar2019	0	3
15007	400003	04Mar2019	0	3
15007	400003	05Mar2019	12	3
15007	400003	06Mar2019	0	3
15007	400003	07Mar2019	0	3
15007	400003	08Mar2019	0	3
15007	400003	09Mar2019	0	3
15007	400003	10Mar2019	0	3
15007	400003	11Mar2019	0	3
15007	400003	12Mar2019	12	3

Desired Output
dest_id	item	ScheduledArrivalDate	qty	MinQ	Desired Output
13827	400003	03Mar2019	0	3	0
13827	400003	04Mar2019	0	3	0
13827	400003	05Mar2019	0	3	0
13827	400003	06Mar2019	0	3	0
13827	400003	07Mar2019	0	3	0
13827	400003	08Mar2019	12	3	4
13827	400003	09Mar2019	0	3	4
13827	400003	10Mar2019	0	3	4
13827	400003	11Mar2019	0	3	0
13827	400003	12Mar2019	0	3	0
15007	400003	03Mar2019	0	3	0
15007	400003	04Mar2019	0	3	0
15007	400003	05Mar2019	12	3	4
15007	400003	06Mar2019	0	3	4
15007	400003	07Mar2019	0	3	4
15007	400003	08Mar2019	0	3	0
15007	400003	09Mar2019	0	3	0
15007	400003	10Mar2019	0	3	4
15007	400003	11Mar2019	0	3	4
15007	400003	12Mar2019	12	3	4

[mkeintz]

What you need to do is:

1. Establish a value for desired_output whenever qty=12
2. Establish a counter to count down the number of records to have that desired_output value.

And you need a way to keep value #1 above and to count down value 2 as the data step iterates through successive observations.  This can be done by holding the values in retained variables (call them _desoutput  and _counter). I.e., learn about the RETAIN statement.

To calculate the retained values when QTY=12, you need an

   if qty=12 then do;

     *** calculate the retained values _desoutput  and _counter ***;

   end;

Then all you need to do, for each and every observation, is to test the value of _counter.  If it's a positive number then permanent variable DESIRED_OUTPUT gets the value of _desoutput.  Otherwise it's a zero.  This is a simple

    if ... then ...;

    else ...;

pair of statements.

Finally decrement the _counter variable by 1.  It doesn't matter if you decrement it to negative value, since it will become positive again only when needed - i.e. when QTY=12.

Then, as long as _counter>0 you should assign the value from _desoutput to desired_output (otherwise assign zero).  And be sure to decrement _counter by 1:

So the logical structure could be something like

[hcbn]

Can you please write a code for the explanation?

[mkeintz]

This has all the earmarks of a homework assignment, especially since I see similar new topics.  Since I won't get credit for the homework, I'm only willing to point you in the proper direction.  That's why I suggested the RETAIN statement, and a group of statement to put inside and an "IF QTY=12 then DO; ....... END;" group.

[Astounding · Accepted solution]

To the extent that I understand the problem, here goes.

Because of the need to spread both forward and backward, you will need to make several passes through the data.  The first pass:

proc sort data=have;
   by dest_id ScheduledArrivalDate;
run;

data first_pass;
   set have;
   by dest_id;
   desired_output = 0;
   if first.dest_id then quantity_to_be_spread = 0;
   if qty = 12 then quantity_to_be_spread + 12;
   if quantity_to_be_spread > 0 then do;
      desired_output = 12 / MinQ;
      quantity_to_be_spread = quantity_to_be_spread - desired_output;
   end;
run;

This spreads the quantities into later dates, but not into earlier dates.  To the extent that some of the spreading has to be done across earlier dates, it takes another pass through the data.

proc sort data=first_pass;
   by dest_id descending ScheduledArrivalDate;
run;

data second_pass;
   set first_pass;
   by dest_id;
   if first.dest_id then do;
      quantity_to_be_spread = 0;
      quantity_already_spread = 0;
   end;
   if qty = 12 then quantity_to_be_spread + 12;
   quantity_already_spread + desired_output;
   if quantity_to_be_spread > quantity_already_spread 
   and desired_output = 0 then do;
      desired_output = 12/MinQ;
      quantity_already_spread + desired_output;
   end;
   drop quantity_to_be_spread quantity_already_spread;
run;

Finally, put the data back into its proper order:

proc sort data=second_pass out=want;
   by dest_id ScheduledArrivalDate;
run;

Just a note .... this is untested code.  So you would need to try it and report back any difficulties.  There are definitely some situations within the data that I could imagine that could cause problems.  But this should handle the basic situation as long as the data behaves.

[hcbn]

@Astounding  Thank you so much. I will try and let you know the result. Thank you so much not ignoring my message

[hcbn]

@Astounding 

Again, thank you so much for your time and energy you put.

The codes are mostly working . There is only one part that does not work.

Please, look at Dest id 13827 corresponding March 22,19 . It only produced two 4 values but it is necessary to be produced three 4 values. Would you please advise if there is anything to correct this part?

run_date	dest_id	item	ScheduledArrivalDate	qty	Minq	desired_output
01Mar2019	13827	400003	03Mar2019	0	3	0
01Mar2019	13827	400003	04Mar2019	0	3	0
01Mar2019	13827	400003	05Mar2019	0	3	0
01Mar2019	13827	400003	06Mar2019	0	3	0
01Mar2019	13827	400003	07Mar2019	0	3	0
01Mar2019	13827	400003	08Mar2019	12	3	4
01Mar2019	13827	400003	09Mar2019	0	3	4
01Mar2019	13827	400003	10Mar2019	0	3	4
01Mar2019	13827	400003	11Mar2019	0	3	0
01Mar2019	13827	400003	12Mar2019	0	3	0
01Mar2019	13827	400003	13Mar2019	0	3	0
01Mar2019	13827	400003	14Mar2019	0	3	0
01Mar2019	13827	400003	15Mar2019	12	3	4
01Mar2019	13827	400003	16Mar2019	0	3	4
01Mar2019	13827	400003	17Mar2019	0	3	4
01Mar2019	13827	400003	18Mar2019	0	3	0
01Mar2019	13827	400003	19Mar2019	0	3	0
01Mar2019	13827	400003	20Mar2019	0	3	0
01Mar2019	13827	400003	21Mar2019	0	3	0
01Mar2019	13827	400003	22Mar2019	12	3	4
01Mar2019	13827	400003	23Mar2019	0	3	4
01Mar2019	15007	400003	03Mar2019	0	2	0
01Mar2019	15007	400003	04Mar2019	0	2	0
01Mar2019	15007	400003	05Mar2019	0	2	0
01Mar2019	15007	400003	06Mar2019	0	2	0
01Mar2019	15007	400003	07Mar2019	0	2	0
01Mar2019	15007	400003	08Mar2019	12	2	6
01Mar2019	15007	400003	09Mar2019	0	2	6
01Mar2019	15007	400003	10Mar2019	0	2	0
01Mar2019	15007	400003	11Mar2019	0	2	0
01Mar2019	15007	400003	12Mar2019	0	2	0
01Mar2019	15007	400003	13Mar2019	0	2	0
01Mar2019	15007	400003	14Mar2019	0	2	0
01Mar2019	15007	400003	15Mar2019	0	2	0
01Mar2019	15007	400003	16Mar2019	0	2	0
01Mar2019	15007	400003	17Mar2019	0	2	0
01Mar2019	15007	400003	18Mar2019	0	2	0
01Mar2019	15007	400003	19Mar2019	0	2	0
01Mar2019	15007	400003	20Mar2019	0	2	0
01Mar2019	15007	400003	21Mar2019	0	2	0
01Mar2019	15007	400003	22Mar2019	12	2	6
01Mar2019	15007	400003	23Mar2019	0	2	6

[Astounding]

Please post the log as well, so I can confirm the code that actually ran.  I will be able to take a look later in the day.

Also, double-check to make sure you are looking at the right data set.  The data you posted is what I would expect FIRST_PASS to look like.

[ballardw]

---

@hcbn wrote:

Hello

If the value is equal to 12 in the qty column then I need to divide the qty column to the MinQ column. The result will be spreading in the desired output until the total qty for each dest id between the March 3 and March 12 is equal the sum of If the desired value.  If the qty is not equal 12, then I will keep the same value in the desired output. 

---

You have posted six  (or more) topics on minor variations of the same problem, with subject lines that do not clearly differentiate the differences.

I am wondering if perhaps you might be in a "forest for the trees" situation where you are attempting to solve little bits of a bigger problem and possibly introducing a lot of other work. I was trying to determine what any of these data sets might be used for and not really coming up with an actual real world use. So if these are not hypothetical "just to learn programming" questions perhaps you can describe what the resulting data sets are actually used for after they are made.

I ask because in the past we have seen similar types of related questions and found someone was attempting to manually do all the steps that other procedures do in the back ground such as Proc Report or Tabulate summarizing and grouping for displays or duplicating one of the Regression procedures.

PS: By now you should know to post example data in the form of a data step and in one of the code boxes opened with either the {I} or "running man" Icon.