CODEWITHSUNDEEP
for-loopwhile-loopsas
Jarom
Jarom

Reputation: 1077

Looping between to tables to create a third table in SAS

I'm trying to write SAS code that tracks that status of raw materials in a manufacturing process. I have two tables of input:

This is the material table:

MATERIAL    AMOUNT  FINISHED    SCRAPPED    NOT_STARTED
A           500     0           0           0
A           500     0           0           0
B           500     0           0           0
B           500     0           0           0
B           500     0           0           0
B           1250    0           0           0
B           750     0           0           0
C           500     0           0           0
C           500     0           0           0
C           500     0           0           0
C           500     0           0           0
C           500     0           0           0

This is the usage table:

COMPANY AMOUNT  TYPE
A       150     FINISHED
A       350     FINISHED
A       200     SCRAPPED
B       300     FINISHED
B       200     SCRAPPED
B      1000     FINISHED
C      1500     FINISHED
C       500     SCRAPPED

Using the FIFO method, I need to write code that allocates all of the "AMOUNT" column to the three columns "FINISHED", "SCRAPPED" and "NOT_STARTED" where "NOT_STARTED" is a calculated field equal to AMOUNT - FINISHED - SCRAPPED.

The output from this instance looks like this:

MATERIAL    AMOUNT  FINISHED    SCRAPPED    NOT_STARTED
A           500     500         0           0
A           500     0           200         300
B           500     300         200         0
B           500     500         0           0
B           500     500         0           0
B          1250     0           0        1250
B           750     0           0         750
C           500     0           0           0
C           500     500         0           0
C           500     500         0           0
C           500     500         0           0
C           500     0           500         0

I've done this in VBA using this code:

y = 2
For x = 2 To raw_material
    not_started = ws_raw_material.Cells(x, 7).Value
    material = ws_raw_material.Cells(x, 1).Value


    Do While not_started > 0 And material = ws_material_usage.Cells(y, 1)

       If usage_amt = 0 Then
          usage_amt = ws_material_usage.Cells(y, 5)
       End If


       If not_started + usage_amt >= 0 Then
           ws_raw_material.Cells(x, 7) = usage_amt + not_started
           If ws_material_usage.Cells(y, 4) = "SCRAPPED" Then
               ws_raw_material.Cells(x, 6) = -usage_amt + ws_raw_material.Cells(x, 6).Value
           Else: ws_raw_material.Cells(x, 5) = -usage_amt + ws_raw_material.Cells(x, 5).Value
           End If

           not_started  = ws_raw_material(x, 7).Value
           usage_amt = 0
           y = y + 1

        Else: ws_raw_material.Cells(x, 7) = 0
           If ws_material_usage.Cells(y, 4) = "SCRAPPED" Then
               ws_raw_material.Cells(x, 6) = remaining + ws_raw_material.Cells(x, 6).Value
           Else: ws_raw_material.Cells(x, 5) = not_started + ws_raw_material.Cells(x, 5).Value
           End If
        redemp_amt = usage_amt + not_started
        remaining = 0
       End If

     Loop
Next x

I need the code in SAS to be compatible with users that want to do this in SAS. I've spend hours trying to directly translate this to SAS but got caught up in the loops and variable value changes.

I don't need anyone to write code for me. I just need some direction to help me think like a SAS programmer. Is there some kind of a procedure that would help? Should I try programming the loops in a data step? Any guidance will help.

Upvotes: 1

Views: 60

Answers (1)

Richard
Richard

Reputation: 27508

The difficulty is the 'many-many' alignment of the material to company that does not allow for a simple merge.

Without writing the code for you I would take this approach to closely emulate the VBA solution.

data stock; input
MATERIAL $  AMOUNT  FINISHED    SCRAPPED    NOT_STARTED; datalines;
A           500     0           0           0
A           500     0           0           0
B           500     0           0           0
B           500     0           0           0
B           500     0           0           0
B           1250    0           0           0
B           750     0           0           0
C           500     0           0           0
C           500     0           0           0
C           500     0           0           0
C           500     0           0           0
C           500     0           0           0
run;

data usage; input
COMPANY $ AMOUNT  TYPE; datalines;
A       150     FINISHED
A       350     FINISHED
A       200     SCRAPPED
B       300     FINISHED
B       200     SCRAPPED
B      1000     FINISHED
C      1500     FINISHED
C       500     SCRAPPED
run;

Apply usage to stock with same looping as VBA, however, use an independent SET statement to advance through the usage. Because your algorithm has usage read flow is occurring at beginning (y=2) and within (y=y+1) you must cause data step to use a single SET statement to read the usage from two different places -- that is the role of the LINK (think GOSUB-ish) in the following pseudo-code.

data ledger;
  if _n_ = 1 then LINK GET_USAGE; * corresponds to retrieving data at (y,*) index data;
  set stock;  * corresponds to looping x and getting data at (x,*);
  /*
      ... vba similar looping logic ...
      ... the (y,*) vba references can be changed to the variable names of data set usage ...
      ... use a LINK GET_USAGE at point where you would do a y=y+1 ...
  */
return; * goes back to top of data step, corresponds to getting next stock row, (or x loop increment);

get_usage:
  set usage (rename=amount=amt_used);  * gets values for company, amount and type;
  * rename prevents variable name collision with stock data;
return;

run;

From SAS Documentation

LINK Statement
Directs program execution immediately to the statement label that is specified and, if followed by a RETURN statement, returns execution to the statement that follows the LINK statement.

Upvotes: 1

Related Questions