如何在Python中提取proc sql与quit;间的内容并存储为列表

问题 #

需要从Python字符串变量中提取所有以proc sql开头、quit;结尾的内容段（包含起止行），并存入列表。

字符串示例 #

row ='''proc sql;
        create table answer_1_stg1 as 
        select  distinct a.CUSTOMER_HIERARCHY_LVL2_CD
                ,b.brand_cd
                ,b.category_cd
                ,a.promo_mechanic_nm
                ,a.promo_mechanic_desc
                ,sum(b.invoice_qty) as sum_invoice
                ,avg(b.invoice_qty) as avg_invoice
                ,max(b.invoice_qty) as max_invoice
                ,min(b.invoice_qty) as min_invoice
        from promo as a
        left join invoice as b
        on 
        a.CUSTOMER_HIERARCHY_LVL2_CD=b.CUSTOMER_HIERARCHY_LVL2_CD and
        a.basecode=b.basecode and
        a.SALESORG_CD=b.SALESORG_CD and
        a.LOCATION_CD=b.LOCATION_CD
        and b.INVOICE_DT between a.event_start_dt and a.event_end_dt
        where year(a.event_start_dt) = 2017 and year(b.INVOICE_DT) = 2017
        group by a.CUSTOMER_HIERARCHY_LVL2_CD
                ,b.brand_cd
                ,b.category_cd
                ,a.promo_mechanic_nm
                ,a.promo_mechanic_desc;
        quit;
        
%sort(answer_1_stg1,descending sum_invoice);

proc sql;
create table answer_1_stg2 as
select distinct promo_mechanic_desc
        ,sum(sum_invoice) as total
from answer_1_stg1;
quit;'''

期望结果 #

lt = ['''proc sql;
        create table answer_1_stg1 as 
        select  distinct a.CUSTOMER_HIERARCHY_LVL2_CD
                ,b.brand_cd
                ,b.category_cd
                ,a.promo_mechanic_nm
                ,a.promo_mechanic_desc
                ,sum(b.invoice_qty) as sum_invoice
                ,avg(b.invoice_qty) as avg_invoice
                ,max(b.invoice_qty) as max_invoice
                ,min(b.invoice_qty) as min_invoice
        from promo as a
        left join invoice as b
        on 
        a.CUSTOMER_HIERARCHY_LVL2_CD=b.CUSTOMER_HIERARCHY_LVL2_CD and
        a.basecode=b.basecode and
        a.SALESORG_CD=b.SALESORG_CD and
        a.LOCATION_CD=b.LOCATION_CD
        and b.INVOICE_DT between a.event_start_dt and a.event_end_dt
        where year(a.event_start_dt) = 2017 and year(b.INVOICE_DT) = 2017
        group by a.CUSTOMER_HIERARCHY_LVL2_CD
                ,b.brand_cd
                ,b.category_cd
                ,a.promo_mechanic_nm
                ,a.promo_mechanic_desc;
        quit;''','''proc sql;
create table answer_1_stg2 as
select distinct promo_mechanic_desc
        ,sum(sum_invoice) as total
from answer_1_stg1;
quit;''']

尝试的代码（未达到预期） #

fm = []

for i in row.split('\n'):
    if "proc sql" in i:
        print()
        fm.append(i.strip())

解决方案 #

方法1：逐行遍历收集（补全原有思路） #

通过添加状态标记，控制内容的收集与终止：

fm = []
current_block = []
collecting = False

for line in row.split('\n'):
    # 触发起始标记，开启收集
    if "proc sql" in line:
        collecting = True
        current_block.append(line)
    # 处于收集状态时，持续添加行
    elif collecting:
        current_block.append(line)
        # 触发终止标记，保存当前块并重置状态
        if "quit;" in line:
            fm.append('\n'.join(current_block))
            current_block = []
            collecting = False

# 查看结果
print(fm)

该方法逻辑直观，适合处理起始/终止标记与其他内容同行的复杂场景。

方法2：正则表达式（简洁高效） #

利用正则非贪婪匹配特性，直接提取所有目标段落：

import re

# 正则规则：匹配proc sql开头到quit;结尾的所有内容，包含换行符
pattern = re.compile(r'proc sql;.*?quit;', re.DOTALL)
fm = pattern.findall(row)

# 查看结果
print(fm)

• re.DOTALL 让.匹配包含换行在内的所有字符
• .*? 为非贪婪匹配，确保每次匹配到最近的quit;就停止，避免合并多个独立块

两种方法均可得到预期的列表结果，可根据实际场景选择使用。

内容的提问来源于stack exchange，提问作者NEM TUDO É O QUE PARECE