求助：基于SAS Return Header条件处理实现数据集格式转换

Hey there! Let's work through this SAS data transformation problem together. Since I can't access the image links you shared, I'll base the solution on a common scenario that matches your description: your source data has "Return Header" rows that contain key metadata, and you need to propagate that header information to all corresponding detail rows to get your target format.

First, let's define a typical version of your source data to ground the solution:

• A Record_Type column flags rows as either Header (the return header rows) or Detail (the line items under each header)
• Header rows contain fields like Return_ID, Return_Date (metadata about the return)
• Detail rows contain item-specific data (like Item_Code, Quantity) but lack the header metadata

Example source data:

Your target format would have each detail row paired with its matching header metadata:

We'll use SAS's RETAIN statement to preserve header values across rows, then filter out the header rows to get the target output:

/* Replace 'source_data' with your actual source dataset name */
data target_data;
    set source_data;
    /* Retain header variables so their values persist across rows */
    retain Return_ID Return_Date;
    
    /* Update retained variables when we hit a Return Header row */
    if Record_Type = 'Header' then do;
        Return_ID = source_data.Return_ID;
        Return_Date = source_data.Return_Date;
        /* Skip writing header rows to the target dataset */
        delete;
    end;
    
    /* Keep only the columns needed for your target format */
    keep Return_ID Return_Date Item_Code Quantity;
run;

• RETAIN Statement: This tells SAS not to reset Return_ID and Return_Date to missing at the start of each data step iteration. So when we update these variables from a header row, all subsequent detail rows will inherit those values until the next header row comes along.
• DELETE Statement: Removes the header rows from the output, since we only want detail rows paired with their metadata.
• KEEP Statement: Ensures we only include the columns required for your target format.

If your data structure differs from the example, tweak the code as needed:

• Modify the if condition to match how your Return Header rows are identified (e.g., if Return_Flag = 'Y' instead of checking Record_Type)
• Update the retain list to include all metadata fields you need to propagate from headers to details
• Adjust the keep statement to match the columns in your target result

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