Do While and If Clause understanding

[David_Billa]

Can someone of you help me understand the below do while with If clause in simple english? I find hard to understand this. Other question is can this be written in better way instead of do while ? 

DATA ATT_SHIP(DROP=J);
	MERGE INVENTORY(IN=I)
		SHIPS_WIDE(IN=F);
	BY LOC VALUE;
	N_ONHAND = COALESCE(ONHAND,0);
	J=1;

	IF FIRST.VALUE THEN
		DO;
			ARRAY SHIPS(*) &SHIP_D2.;
		END;

	DO WHILE (N_ONHAND >0 and J <= DIM(SHIPS));
		SHIPS[J] = COALESCE(SHIPS[J],0);

		IF INPUT(SUBSTR(VNAME(SHIPS[J]),5),mmddyy10.)<=STOPSHIP_DT THEN
			DO;
				IF N_ONHAND > SHIPS[J] THEN
					DO;
						N_ONHAND = N_ONHAND-SHIPS[J];
						SHIPS[J]=0;
					END;
				ELSE IF SHIPS[J] > N_ONHAND THEN
					DO;
						SHIPS[J] = SHIPS[J]-N_ONHAND;
						N_ONHAND=0;
					END;
				ELSE
					DO;
						SHIPS[J]=0;
						N_ONHAND=0;
					END;
			END;

		J+1;
	END;

	IF LAST.VALUE THEN
		DO;
			REM_DEMAND_SHIP = SUM(of SHIP:);
		END;
RUN;

[pink_poodle]

N_OnHand is how many items you have in stock. Ships is how many times you need to ship them. You also have stopship_dt when to stop shipping (e.g., milk spoils or something). The while clause says while you have items on hand and they are being ordered do this:
Scenario 1: if the order date is before stop-ship date, then subtract items ordered from number on hand because everything is ok and you can go ahead and ship them.
Scenario 2: The second if clause is for when they ordered more than you have, so on-hand is zero and you are also a little in debt (so there are some remaining ships).
Scenario 3: The third if scenario is fair - they ordered as many items as you have, so no more items and no more orders.

[ballardw]

Apparently someone has poorly structured data and has placed important data in the names of multiple variables. This bit is getting the date information from the variable name and then comparing with some date variable

		IF INPUT(SUBSTR(VNAME(SHIPS[J]),5),mmddyy10.)<=STOPSHIP_DT THEN

For what it may be worth the IF in this bit is useless as Array statements are declarative not executable.

IF FIRST.VALUE THEN
		DO;
			ARRAY SHIPS(*) &SHIP_D2.;
		END;

 Should just be

	ARRAY SHIPS(*) &SHIP_D2.;

I am going to guess that if instead of that Ships_wide data set one with a proper date variable and long structure that Proc SQL would combine the proper values with an ON like a.shipdate <= b.Stopship_dt and a case to do the inventory (guess) juggling.

[Tom]

It probably helps to eliminate a lot of that white space so a human can few more of the code in a single frame of vision.

While at it also remove the IF/THEN around the ARRAY statement as that is doing nothing other than confusing novice SAS programmers.  There is also no need to manually increment the loop counter J. In a data step you can have an IF with both an iteration variable and a WHILE() clause.

DATA ATT_SHIP(DROP=J);
  MERGE INVENTORY(IN=I)
        SHIPS_WIDE(IN=F)
  ;
  BY LOC VALUE;
  ARRAY SHIPS(*) &SHIP_D2.;
  N_ONHAND = COALESCE(ONHAND,0);
  DO J=1 TO DIM(SHIPS) WHILE (N_ONHAND >0);
    SHIPS[J] = COALESCE(SHIPS[J],0);
    IF INPUT(SUBSTR(VNAME(SHIPS[J]),5),mmddyy10.)<=STOPSHIP_DT THEN DO;
      IF N_ONHAND > SHIPS[J] THEN DO;
        N_ONHAND = N_ONHAND-SHIPS[J];
        SHIPS[J]=0;
      END;
      ELSE IF SHIPS[J] > N_ONHAND THEN DO;
        SHIPS[J] = SHIPS[J]-N_ONHAND;
        N_ONHAND=0;
      END;
      ELSE DO;
        SHIPS[J]=0;
        N_ONHAND=0;
      END;
    END;
  END;
  IF LAST.VALUE THEN DO;
    REM_DEMAND_SHIP = SUM(of SHIP:);
  END;
RUN;

It would also make it easier to read if everything wasn't in uppercase.

On each observation it loops over the list of variables provided by the macro variable SHIP_D2 treating the values as a "demand".  The goal appears to be to try to figure out whether the demands can be met by the amount ONHAND.  At the end the values in the individual variables will be reduced by the amount of their demand that could be met by what is on hand and the new variable N_ONHAND will reflect how much "amount" will be left when those demands were met.

The extra IF that is checking the variable name against a date will skip the variables  in the list whose name indicates they are in the future (when compared the STOPSHIP_DT value).

What the last IF is doing is hard to explain.  It seems to be creating a new variable to store the total demand that could not be met (including those that are in the future).  But it only does it for some of the observations.  However nothing above is doing anything else differently for observations that are not the last when grouped by LOC and VALUE so it is not clear what meaning could be applied to this new variable.  

Perhaps the last IF makes sense if the variables in the ARRAY are only coming from the SHIPS_WIDE dataset and the MERGE is doing a MANY to ONE merge between INVENTORY and SHIPS_WIDE.  Then since those variables are coming from an input dataset they will be retained across all of the observations from INVENTORY that match.  Hence at the last observations the amount left will be the final amount left.  But you could have also just generated the variable on every observation in the group and then you could see a running sum (subtraction) of the amount of unmet demand.

[David_Billa]

May I request you to explain the second If clause where they calculate
N_ONHAND by subtracting with SHIPS?

How the calculation of N_ONHAND is being performed here?

[Tom]

---

@David_Billa wrote:
May I request you to explain the second If clause where they calculate
N_ONHAND by subtracting with SHIPS?

How the calculation of N_ONHAND is being performed here?

---

That is what is matching the amount available to meet the demand from the current "ship". It removes the amount that could be supplied from both the amount requested and from the amount on hand.  The IF/THEN/ELSE logic is just to prevent negative results.

Note that if you add another variable you don't need the IF/THEN logic.  You can just use MIN and MAX functions instead.

Here is an example that makes new variables so you can see the before and after amounts.

data test;
  input n_onhand ship ;
  amount=min(ship,n_onhand);
  n_onhand_new=max(0,n_onhand-amount);
  ship_new=max(0,ship-amount);
cards;
100 10
10 100
20 20
30 0
0 30
;
proc print;
run;

Results

                                     n_onhand_
Obs    n_onhand    ship    amount       new       ship_new

 1        100        10      10          90           0
 2         10       100      10           0          90
 3         20        20      20           0           0
 4         30         0       0          30           0
 5          0        30       0           0          30

[David_Billa]

@Tom thank yoyu. Your code is working fine for first If with min and max. In similar way, I'd like to know how can I write the remaining else if as shown below with min and max function.

ELSE IF SHIPS[J] > N_ONHAND THEN
					DO;
						SHIPS[J] = SHIPS[J]-N_ONHAND;
						N_ONHAND=0;
					END;
				ELSE
					DO;
						SHIPS[J]=0;
						N_ONHAND=0;
					END;
			END;

		J+1;

[Tom]

The IF/THEN/ELSE/IF/ELSE series in the original just divides the problem into three cases.  That is not needed in my example.  Check the example data I used.  It has some where SHIP>ON_HAND and some where SHIP<ON_HAND and some where there were equal.  

You don't have to change the current code if you don't want to, but it just seemed easier to me to understand since it first determines how many units can be supplied (the MIN() and then subtracts them.

Try it without the MAX() function calls.  It also works. 

data test;
  input n_onhand ship ;
  amount=min(ship,n_onhand);
  n_onhand_new=n_onhand-amount;
  ship_new=ship-amount;
cards;
100 10
10 100
20 20
30 0
0 30
;

I had the MAX because I was trying at first to do it without first calculating the possible amount.  In that case the MAX() was needed to prevent negative results.   It  works with my example where it is creating new variables.

data test;
  input n_onhand ship ;
  n_onhand_new=max(0,n_onhand-ship);
  ship_new=max(0,ship-n_onhand);
cards;
100 10
10 100
20 20
30 0
0 30
;

But it cannot work without first calculating the amount if you are going to modify the input variables since the first subtraction would then make the second impossible.