如何使用SAS或SQL求解满足剂量要求的药瓶规格最优组合方案

基础规则梳理 #

• 可选药瓶规格：400mg、200mg、80mg，单规格使用数量必须为非负整数
• 总剂量约束：840mg ≤ 总剂量 ≤ 919mg（上限按规则计算为840+最小规格80-1）
• 优化优先级：优先取总剂量最接近840mg的方案，相同总剂量下优先总瓶数更少的方案

最优方案结论 #

存在刚好满足840mg的组合，为最优解，所有符合要求的840mg组合如下：

• 0个400mg + 1个200mg + 8个80mg
• 0个400mg + 3个200mg + 3个80mg
• 1个400mg + 1个200mg + 3个80mg

SQL实现代码 #

核心逻辑为枚举所有可能的单规格数量组合，筛选符合约束后按优化目标排序取结果，因规格少、数量上限低，枚举计算量可忽略：

WITH cnt_400_list AS (
    SELECT 0 AS cnt_400 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
),
cnt_200_list AS (
    SELECT 0 AS cnt_200 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
),
cnt_80_list AS (
    SELECT 0 AS cnt_80 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
    UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
)
SELECT 
    cnt_400, cnt_200, cnt_80,
    cnt_400*400 + cnt_200*200 + cnt_80*80 AS total_dosage,
    cnt_400 + cnt_200 + cnt_80 AS total_bottles
FROM cnt_400_list, cnt_200_list, cnt_80_list
WHERE total_dosage BETWEEN 840 AND 919
ORDER BY total_dosage ASC, total_bottles ASC

执行后排序最靠前的结果即为最优方案。

SAS实现代码 #

提供两种实现方式，逻辑和SQL一致：

数据步循环实现 #

data medicine_combine;
    * 定义各规格最大可能数量，避免无效循环;
    max_400 = ceil(919/400);
    max_200 = ceil(919/200);
    max_80 = ceil(919/80);
    
    do cnt_400 = 0 to max_400;
        do cnt_200 = 0 to max_200;
            do cnt_80 = 0 to max_80;
                total_dosage = cnt_400*400 + cnt_200*200 + cnt_80*80;
                total_bottles = cnt_400 + cnt_200 + cnt_80;
                if 840 <= total_dosage <= 919 then output;
            end;
        end;
    end;
run;

* 按优化目标排序;
proc sort data=medicine_combine;
    by total_dosage ascending total_bottles ascending;
run;

* 打印前10条最优结果;
proc print data=medicine_combine(obs=10) noobs;
    title "药用瓶最优组合方案";
run;

PROC SQL实现 #

和通用SQL逻辑完全一致，可直接在SAS环境运行：

proc sql;
    create table medicine_combine_sql as
    select 
        a.cnt_400, b.cnt_200, c.cnt_80,
        a.cnt_400*400 + b.cnt_200*200 + c.cnt_80*80 as total_dosage,
        calculated total_dosage - 840 as diff_dosage,
        a.cnt_400 + b.cnt_200 + c.cnt_80 as total_bottles
    from 
        (select 0 as cnt_400 from sashelp.class(obs=1) union all select 1 from sashelp.class(obs=1) union all select 2 from sashelp.class(obs=1) union all select 3 from sashelp.class(obs=1)) a,
        (select 0 as cnt_200 from sashelp.class(obs=1) union all select 1 from sashelp.class(obs=1) union all select 2 from sashelp.class(obs=1) union all select 3 from sashelp.class(obs=1) union all select 4 from sashelp.class(obs=1) union all select 5 from sashelp.class(obs=1)) b,
        (select 0 as cnt_80 from sashelp.class(obs=1) union all select 1 from sashelp.class(obs=1) union all select 2 from sashelp.class(obs=1) union all select 3 from sashelp.class(obs=1) union all select 4 from sashelp.class(obs=1) union all select 5 from sashelp.class(obs=1)
         union all select 6 from sashelp.class(obs=1) union all select 7 from sashelp.class(obs=1) union all select 8 from sashelp.class(obs=1) union all select 9 from sashelp.class(obs=1) union all select 10 from sashelp.class(obs=1) union all select 11 from sashelp.class(obs=1) union all select 12 from sashelp.class(obs=1)) c
    where calculated total_dosage between 840 and 919
    order by total_dosage asc, total_bottles asc;
quit;

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