SAS两表循环生成第三表：基于FIFO的原材料状态分配需求

Alright, let's translate your VBA FIFO raw material tracking logic into SAS—this is a common manufacturing use case, so I'll break it down step by step with clear, testable code that aligns with your requirement to split AMOUNT into FINISHED, SCRAPPED, and calculated NOT_STARTED.

First, let's define sample input tables that match the structure you described (feel free to adjust variable names to match your actual data):

/* Sample Material Table: Raw material batches, sorted by receipt date for FIFO */
data material;
    input material_id $ batch_no $ amount receipt_date :date9.;
    format receipt_date date9.;
    datalines;
MAT001 B001 500 01JAN2024
MAT001 B002 300 05JAN2024
MAT002 B003 200 02JAN2024
;
run;

/* Sample Usage Table: Finished and scrapped quantities per material */
data usage;
    input material_id $ finished_qty scrapped_qty;
    datalines;
MAT001 600 150
MAT002 180 10
;
run;

Step 1: Prepare Material Batches for FIFO Processing #

First, we sort the material table by material ID and receipt date to enforce FIFO order. We also calculate running totals to track cumulative available quantity per material—this helps us manage allocation across batches.

proc sort data=material;
    by material_id receipt_date;
run;

data material_running;
    set material;
    by material_id;
    retain running_total;
    
    /* Reset running total for the first batch of each material */
    if first.material_id then running_total = 0;
    running_total + amount;
    
    /* Track the start/end of cumulative quantity for each batch */
    batch_start = running_total - amount;
    batch_end = running_total;
run;

Step 2: Merge Material Batches with Usage Data #

We join the prepared material batches with the usage table to bring in the finished and scrapped quantities we need to allocate.

proc sql;
    create table material_usage as
    select 
        m.material_id,
        m.batch_no,
        m.amount,
        m.receipt_date,
        m.batch_start,
        m.batch_end,
        u.finished_qty,
        u.scrapped_qty
    from material_running m
    left join usage u
        on m.material_id = u.material_id;
quit;

Step 3: Core FIFO Allocation Logic #

This data step is where we handle the actual allocation. We iterate through each batch per material, first allocating to FINISHED, then SCRAPPED, and calculate NOT_STARTED as the leftover quantity.

data final_allocation;
    set material_usage;
    by material_id;
    retain remaining_finished remaining_scrapped;
    
    /* Initialize remaining quantities for the first batch of each material */
    if first.material_id then do;
        remaining_finished = finished_qty;
        remaining_scrapped = scrapped_qty;
    end;

    /* Allocate to FINISHED first */
    if remaining_finished > 0 then do;
        if amount <= remaining_finished then do;
            finished_allocated = amount;
            remaining_finished = remaining_finished - amount;
        end;
        else do;
            finished_allocated = remaining_finished;
            remaining_finished = 0;
        end;
    end;
    else finished_allocated = 0;

    /* Allocate to SCRAPPED using the batch's remaining quantity after finished allocation */
    batch_remaining_after_finished = amount - finished_allocated;
    if remaining_scrapped > 0 and batch_remaining_after_finished > 0 then do;
        if batch_remaining_after_finished <= remaining_scrapped then do;
            scrapped_allocated = batch_remaining_after_finished;
            remaining_scrapped = remaining_scrapped - batch_remaining_after_finished;
        end;
        else do;
            scrapped_allocated = remaining_scrapped;
            remaining_scrapped = 0;
        end;
    end;
    else scrapped_allocated = 0;

    /* Calculate NOT_STARTED as the leftover quantity */
    not_started = amount - finished_allocated - scrapped_allocated;

    /* Clean up output fields to match your required column names */
    keep material_id batch_no amount receipt_date finished_allocated scrapped_allocated not_started;
    rename finished_allocated=FINISHED scrapped_allocated=SCRAPPED;
run;

Step 4: Verify the Results #

Run this print step to confirm the FIFO allocation works as expected:

proc print data=final_allocation;
    title "FIFO Raw Material Allocation Results";
run;

Key Notes: #

• If your business logic requires prioritizing SCRAPPED over FINISHED, simply swap the order of the allocation blocks in the data step.
• The retain statement is critical here—it keeps track of leftover quantities across batches for the same material.
• The left join ensures we handle materials that might not have corresponding usage entries (their FINISHED/SCRAPPED will be missing, so NOT_STARTED will equal AMOUNT).

内容的提问来源于stack exchange，提问作者Jarom