提升SAS交叉连接效率的技术问询：权重组合计算优化

Your current approach of using a full cross-join makes sense for small datasets, but it quickly becomes infeasible as your input size grows—with 460k observations × 5151 valid weight combinations, you're looking at over 2.3 billion rows of intermediate data, which explains the 1-hour runtime. Scaling to 465k observations would make this even worse.

The key insight here is that you don't need to generate that massive cross-joined table at all. Since your end goal is to calculate Somers' D for each weight combination, you can instead compute this statistic directly for each weight set without duplicating your source data thousands of times.

Why Your Current Method Is Slow #

Cross-joins create a multiplicative explosion of data:

• Valid weight combinations: (100+1)*(100+2)/2 = 5151 (small, manageable)
• Source data rows: 460k → 460k × 5151 = 2.36B rows of intermediate data
• This kills I/O performance and consumes massive memory.

Optimized Solutions #

Below are two efficient approaches to achieve your goal without generating the cross-joined table.

1. SAS/IML (Fastest for Large Datasets) #

SAS/IML uses matrix operations to batch-process all weight combinations in memory, avoiding repetitive I/O. This is ideal for large datasets.

/* Step 1: Generate your weight matrix (unchanged, still small) */
DATA w_matrix;
RETAIN model_combination 0;
DO n_1 = 0 TO 100 BY 1;
  DO n_2 = 0 TO 100 BY 1;
    n_3 = 100 - n_1 - n_2;
    IF n_3 BETWEEN 0 AND 100 THEN DO;
      model_combination+1;
      w_1 = n_1/100;
      w_2 = n_2/100;
      w_3 = n_3/100;
      OUTPUT;
    END;
  END;
END;
DROP n_1 n_2 n_3;
RUN;

/* Step 2: Use IML for matrix-based Somers' D calculation */
PROC IML;
  /* Load source data (scores + target variable) */
  USE sashelp.baseball;
    READ ALL VAR {crhits natbat nbb logsalary} INTO data_mat;
  CLOSE sashelp.baseball;

  /* Load weight matrix */
  USE w_matrix;
    READ ALL VAR {model_combination w_1 w_2 w_3} INTO weights;
  CLOSE w_matrix;

  /* Calculate all weighted scores in one matrix operation */
  /* data_mat[,1:3] = 3 score columns; weights[,2:4] = weight columns */
  weighted_scores = data_mat[,1:3] * weights[,2:4]`;

  /* Define function to compute Somers' D between two variables */
  START somers_d(target, predictor);
    n = NROW(target);
    /* Generate all pairwise indices */
    pairs = COMB(n, 2);
    t1 = target[pairs[,1]];
    t2 = target[pairs[,2]];
    p1 = predictor[pairs[,1]];
    p2 = predictor[pairs[,2]];

    /* Count concordant, discordant, and tied pairs */
    concordant = SUM( (t1>t2 & p1>p2) | (t1<t2 & p1<p2) );
    discordant = SUM( (t1>t2 & p1<p2) | (t1<t2 & p1>p2) );
    tied_pred = SUM(p1=p2);

    /* Compute Somers' D (adjust denominator based on your definition) */
    somers = (concordant - discordant) / (concordant + discordant + tied_pred);
    RETURN(somers);
  FINISH;

  /* Compute Somers' D for every weight combination */
  somers_results = J(NCOL(weighted_scores), 1, .);
  DO i = 1 TO NCOL(weighted_scores);
    somers_results[i] = somers_d(data_mat[,4], weighted_scores[,i]);
  END;

  /* Combine results with weight metadata */
  final_results = weights[,1] || weights[,2:4] || somers_results;
  CREATE somers_d_results FROM final_results 
    COLNAME={model_combination w_1 w_2 w_3 somers_d};
  APPEND FROM final_results;
QUIT;

2. Macro Loop (No IML License Required) #

If you don't have access to SAS/IML, use a macro loop to process each weight combination individually. This avoids the cross-join and only processes your source data 5151 times (still way faster than generating 2B rows).

/* Step 1: Generate weight matrix (same as before) */
DATA w_matrix;
RETAIN model_combination 0;
DO n_1 = 0 TO 100 BY 1;
  DO n_2 = 0 TO 100 BY 1;
    n_3 = 100 - n_1 - n_2;
    IF n_3 BETWEEN 0 AND 100 THEN DO;
      model_combination+1;
      w_1 = n_1/100;
      w_2 = n_2/100;
      w_3 = n_3/100;
      OUTPUT;
    END;
  END;
END;
DROP n_1 n_2 n_3;
RUN;

/* Step 2: Create macro variables for weight combinations */
PROC SQL NOPRINT;
  SELECT model_combination, w_1, w_2, w_3
    INTO :mc_list SEPARATED BY ' ',
         :w1_list SEPARATED BY ' ',
         :w2_list SEPARATED BY ' ',
         :w3_list SEPARATED BY ' '
  FROM w_matrix;
  %let total_combinations = &SQLOBS;
QUIT;

/* Step 3: Initialize empty results table */
DATA somers_d_results;
  LENGTH model_combination 8 w_1 w_2 w_3 8 somers_d 8;
  STOP;
RUN;

/* Step 4: Macro to compute Somers' D for each weight set */
%MACRO calc_somers;
  %DO i = 1 %TO &total_combinations;
    %LET mc = %SCAN(&mc_list, &i);
    %LET w1 = %SCAN(&w1_list, &i);
    %LET w2 = %SCAN(&w2_list, &i);
    %LET w3 = %SCAN(&w3_list, &i);

    /* Use PROC LOGISTIC to get Somers' D (ideal for continuous target) */
    PROC LOGISTIC DATA=sashelp.baseball NOPRINT;
      MODEL logsalary = y_hat_int;
      COMPUTE y_hat_int = &w1 * crhits + &w2 * natbat + &w3 * nbb;
      ODS OUTPUT Association=assoc_stats;
    RUN;

    /* Extract Somers' D from output */
    DATA temp_result;
      SET assoc_stats;
      WHERE Statistic = "Somers' D";
      model_combination = &mc;
      w_1 = &w1;
      w_2 = &w2;
      w_3 = &w3;
      somers_d = Value;
      KEEP model_combination w_1 w_2 w_3 somers_d;
    RUN;

    /* Append to results table */
    PROC APPEND BASE=somers_d_results DATA=temp_result;
    RUN;
  %END;
%MEND;

%calc_somers;

Key Optimization Takeaways #

1. Avoid cross-joins for large data: They create unnecessary data bloat.
2. Leverage small weight count: With only 5151 combinations, processing each one individually is feasible.
3. Use in-memory operations: SAS/IML drastically reduces I/O overhead compared to disk-based processing.
4. Use built-in procs: PROC LOGISTIC or PROC FREQ can compute Somers' D directly without manual pair counting.

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