创建膳食三分位数后pms.final_data3与pms.bhs样本量差异排查

问题背景 #

原数据集pms.bhs包含101条观测值，运行下方SAS代码生成膳食三分位数变量后，输出数据集pms.final_data3的样本量仅剩余67条，需排查原因并确保保留全部101名参与者的数据。

运行的SAS代码 #

data pms.final_data3;
  set pms.final_data3;
  if Niacin_mg = . then Niacin_Tertile = .;
  else if Niacin_mg <= 16.67 then Niacin_Tertile = 1; /* Lowest tertile */
  else if Niacin_mg > 16.67 and Niacin_mg_1 <= 27.1 then Niacin_Tertile = 2; /* Middle tertile */
  else Niacin_Tertile = 3; /* Highest tertile */
  if Riboflavin_mg = . then Riboflavin_Tertile = .;
  else if Riboflavin_mg <= 1.38 then Riboflavin_Tertile = 1;
  else if Riboflavin_mg > 1.38 and Ribofalvin_mg <= 2.34 then Riboflavin_Tertile = 2;
  else Riboflavin_Tertile = 3;
  if VitaminB6_mg = . then VitaminB6_Tertile = .;
  else if VitaminB6_mg <= 1.48 then VitaminB6_Tertile = 1;
  else if VitaminB6_mg_1 > 1.48 and VitaminB6_mg <= 2.41 then VitaminB6_Tertile = 2;
  else VitaminB6_Tertile =3;
  if Totalfolate_mg = . then Totalfolate_Tertile = .;
  else if Totalfolate_mg <= 0.3009 then Totalfolate_Tertile = 1;
  else if Totalfolate_mg > 0.3009 and Totalfolate_mg <= 0.44942 then Totalfolate_Tertile = 2;
  else Totalfolate_Tertile =3;
  if VitaminB12_mg = . then VitaminB12_Tertile = .;
  else if VitaminB12_mg <= .00281 then VitaminB12_Tertile = 1;
  else if VitaminB12_mg > .00281 and VitaminB12_mg <= .00492 then VitaminB12_Tertile = 2;
  else VitaminB12_Tertile =3;
  if Potassium_mg = . then Potassium_Tertile = .;
  else if Potassium_mg <= 1696.36 then Potassium_Tertile = 1;
  else if Potassium_mg > 1696.36 and Potassium_mg <= 2783.33 then Potassium_Tertile = 2;
  else Potassium_Tertile = 3;
  if Magnesium_mg = . then Magnesium_Tertile = .;
  else if Magnesium_mg <= 244.69 then Magnesium_Tertile = 1;
  else if Magnesium_mg > 244.69 and Magnesium_mg <= 366.14 then Magnesium_Tertile = 2;
  else Magnesium_Tertile = 3;
  if Manganese_mg = . then Manganese_Tertile = .;
  else if Manganese_mg <= 2.33 then Manganese_Tertile = 1;
  else if Manganese_mg > 2.33 and Manganese_mg <= 3.59 then Manganese_Tertile = 2;
  else Manganese_Tertile = 3;
  if VitaminD_mg = . then VitaminD_Tertile = .;
  else if VitaminD_mg <= .00252 then VitaminD_Tertile = 1;
  else if VitaminD_mg > .00252 and VitaminD_mg <= .00497 then VitaminD_Tertile = 2;
  else VitaminD_Tertile =3;
  if Calcium_mg = . then Calcium_Tertile = .;
  else if Calcium_mg <= 852.91 then Calcium_Tertile = 1;
  else if Calcium_mg > 852.91 and Calcium_mg <= 1218.04 then Calcium_Tertile = 2;
  else Calcium_Tertile =3;
  if Zinc_mg = . then Zinc_Tertile =.;
  else if Zinc_mg <= 6.91 then Zinc_Tertile = 1;
  else if Zinc_mg > 6.91 and Zinc_mg <= 10.93 then Zinc_Tertile = 2;
  else Zinc_Tertile =3;
  if Caffeine_mg = . then Caffeine_Tertile = .;
  else if Caffeine_mg <= 27.27 then Caffeine_Tertile = 1;
  else if Caffeine_mg > 27.27 and Caffeine_mg <= 131.59 then Caffeine_Tertile = 2;
  else Caffeine_Tertile =3;
run;

核心问题排查 #

1. 数据源错误：代码中set pms.final_data3;指定从final_data3读取数据，但原始数据集是pms.bhs。如果final_data3本身已被处理为67条记录，此代码只会基于现有67条生成变量，无法获取原始101条数据。
2. 变量名拼写错误：
   • 核黄素判断中，Ribofalvin_mg是拼写错误，正确应为Riboflavin_mg
   • 烟酸、维生素B6判断中，错误引用了Niacin_mg_1、VitaminB6_mg_1（原始数据集大概率不存在该变量），会导致条件判断异常
3. 逻辑漏洞：错误变量会被SAS视为缺失值，导致部分观测值的三分位数分类逻辑失效，但不会直接删除样本，样本量减少的主因还是数据源错误。

修正方案 #

1. 修正数据源与变量名 #

将代码改为从原始数据集pms.bhs读取，同时修正所有拼写错误的变量名：

data pms.final_data3;
  set pms.bhs; /* 改为读取原始数据集 */
  /* 烟酸三分位数（修正Niacin_mg_1为Niacin_mg） */
  if Niacin_mg = . then Niacin_Tertile = .;
  else if Niacin_mg <= 16.67 then Niacin_Tertile = 1;
  else if Niacin_mg > 16.67 and Niacin_mg <= 27.1 then Niacin_Tertile = 2;
  else Niacin_Tertile = 3;
  /* 核黄素三分位数（修正Ribofalvin_mg为Riboflavin_mg） */
  if Riboflavin_mg = . then Riboflavin_Tertile = .;
  else if Riboflavin_mg <= 1.38 then Riboflavin_Tertile = 1;
  else if Riboflavin_mg > 1.38 and Riboflavin_mg <= 2.34 then Riboflavin_Tertile = 2;
  else Riboflavin_Tertile = 3;
  /* 维生素B6三分位数（修正VitaminB6_mg_1为VitaminB6_mg） */
  if VitaminB6_mg = . then VitaminB6_Tertile = .;
  else if VitaminB6_mg <= 1.48 then VitaminB6_Tertile = 1;
  else if VitaminB6_mg > 1.48 and VitaminB6_mg <= 2.41 then VitaminB6_Tertile = 2;
  else VitaminB6_Tertile =3;
  /* 其余变量逻辑保持不变 */
  if Totalfolate_mg = . then Totalfolate_Tertile = .;
  else if Totalfolate_mg <= 0.3009 then Totalfolate_Tertile = 1;
  else if Totalfolate_mg > 0.3009 and Totalfolate_mg <= 0.44942 then Totalfolate_Tertile = 2;
  else Totalfolate_Tertile =3;
  if VitaminB12_mg = . then VitaminB12_Tertile = .;
  else if VitaminB12_mg <= .00281 then VitaminB12_Tertile = 1;
  else if VitaminB12_mg > .00281 and VitaminB12_mg <= .00492 then VitaminB12_Tertile = 2;
  else VitaminB12_Tertile =3;
  if Potassium_mg = . then Potassium_Tertile = .;
  else if Potassium_mg <= 1696.36 then Potassium_Tertile = 1;
  else if Potassium_mg > 1696.36 and Potassium_mg <= 2783.33 then Potassium_Tertile = 2;
  else Potassium_Tertile = 3;
  if Magnesium_mg = . then Magnesium_Tertile = .;
  else if Magnesium_mg <= 244.69 then Magnesium_Tertile = 1;
  else if Magnesium_mg > 244.69 and Magnesium_mg <= 366.14 then Magnesium_Tertile = 2;
  else Magnesium_Tertile = 3;
  if Manganese_mg = . then Manganese_Tertile = .;
  else if Manganese_mg <= 2.33 then Manganese_Tertile = 1;
  else if Manganese_mg > 2.33 and Manganese_mg <= 3.59 then Manganese_Tertile = 2;
  else Manganese_Tertile = 3;
  if VitaminD_mg = . then VitaminD_Tertile = .;
  else if VitaminD_mg <= .00252 then VitaminD_Tertile = 1;
  else if VitaminD_mg > .00252 and VitaminD_mg <= .00497 then VitaminD_Tertile = 2;
  else VitaminD_Tertile =3;
  if Calcium_mg = . then Calcium_Tertile = .;
  else if Calcium_mg <= 852.91 then Calcium_Tertile = 1;
  else if Calcium_mg > 852.91 and Calcium_mg <= 1218.04 then Calcium_Tertile = 2;
  else Calcium_Tertile =3;
  if Zinc_mg = . then Zinc_Tertile =.;
  else if Zinc_mg <= 6.91 then Zinc_Tertile = 1;
  else if Zinc_mg > 6.91 and Zinc_mg <= 10.93 then Zinc_Tertile = 2;
  else Zinc_Tertile =3;
  if Caffeine_mg = . then Caffeine_Tertile = .;
  else if Caffeine_mg <= 27.27 then Caffeine_Tertile = 1;
  else if Caffeine_mg > 27.27 and Caffeine_mg <= 131.59 then Caffeine_Tertile = 2;
  else Caffeine_Tertile =3;
run;

2. 验证样本量 #

运行修正代码后，执行以下语句确认样本量：

proc sql;
  select count(*) as sample_count from pms.final_data3;
quit;

3. 避免覆盖现有数据（可选） #

若pms.final_data3已有重要数据，建议输出到新数据集，防止覆盖：

data pms.final_data_tertiles;
  set pms.bhs;
  /* 三分位数判断代码 */
run;

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