sql - 如何将数值格式化为第 3 位，同时在 SAS 中将其均匀到第 2 位

Question

我正在尝试解决 SAS EG 中的数字格式问题，希望您能给我一些支持。

基本上，我收到的带有数字数据的变量有 3 个十进制数字，但我被要求将这些数据转换为显示 3 个数字的格式，但它会将它们平均到第 2 个数字。

我需要实现的是这样的：

prize_old = 14543.567  prize_new = 14543.570
prize_old = 647473.543 prize_new = 647473.540

此外，如果您看到他们要求我编辑的代码，最终变量prize_string_n 是字符串，是由符号和数字之间的连接产生的，因此我需要在cats 函数之前进行干预。

data new;
set old;
prize_01 = sum(effect_2021_1, effect_trash_T_2021_1, effect_trash_C_2021_1);
prize_02 = sum(effect_2021_2, effect_trash_T_2021_2, effect_trash_C_2021_2);
prize_03 = sum(effect_2021_3, effect_trash_T_2021_3, effect_trash_C_2021_3);
prize_04 = sum(effect_2021_4, effect_trash_T_2021_4, effect_trash_C_2021_4);
prize_05 = sum(effect_2021_5, effect_trash_T_2021_5, effect_trash_C_2021_5);
prize_06 = sum(effect_2021_6, effect_trash_T_2021_6, effect_trash_C_2021_6);
prize_07 = sum(effect_2021_7, effect_trash_T_2021_7, effect_trash_C_2021_7);
prize_08 = sum(effect_2021_8, effect_trash_T_2021_8, effect_trash_C_2021_8);
prize_09 = sum(effect_2021_9, effect_trash_T_2021_9, effect_trash_C_2021_9);
prize_10 = sum(effect_2021_10, effect_trash_T_2021_10, effect_trash_C_2021_10);
prize_11 = sum(effect_2021_11, effect_trash_T_2021_11, effect_trash_C_2021_11);
prize_12 = sum(effect_2021_12, effect_trash_T_2021_12, effect_trash_C_2021_12);

if prize_01 = . then prize_01 = 0;
if prize_02 = . then prize_02 = 0;
if prize_03 = . then prize_03 = 0;
if prize_04 = . then prize_04 = 0;
if prize_05 = . then prize_05 = 0;
if prize_06 = . then prize_06 = 0;
if prize_07 = . then prize_07 = 0;
if prize_08 = . then prize_08 = 0;
if prize_09 = . then prize_09 = 0;
if prize_10 = . then prize_10 = 0;
if prize_11 = . then prize_11 = 0;
if prize_12 = . then prize_12 = 0;

if prize_01 >= 0 then SIGN_01 = '+';
if prize_02 >= 0 then SIGN_02 = '+';
if prize_03 >= 0 then SIGN_03 = '+';
if prize_04 >= 0 then SIGN_04 = '+';
if prize_05 >= 0 then SIGN_05 = '+';
if prize_06 >= 0 then SIGN_06 = '+';
if prize_07 >= 0 then SIGN_07 = '+';
if prize_08 >= 0 then SIGN_08 = '+';
if prize_09 >= 0 then SIGN_09 = '+';
if prize_10 >= 0 then SIGN_10 = '+';
if prize_11 >= 0 then SIGN_11 = '+';
if prize_12 >= 0 then SIGN_12 = '+';

if prize_01 < 0 then SIGN_01 = '-';
if prize_02 < 0 then SIGN_02 = '-';
if prize_03 < 0 then SIGN_03 = '-';
if prize_04 < 0 then SIGN_04 = '-';
if prize_05 < 0 then SIGN_05 = '-';
if prize_06 < 0 then SIGN_06 = '-';
if prize_07 < 0 then SIGN_07 = '-';
if prize_08 < 0 then SIGN_08 = '-';
if prize_09 < 0 then SIGN_09 = '-';
if prize_10 < 0 then SIGN_10 = '-';
if prize_11 < 0 then SIGN_11 = '-';
if prize_12 < 0 then SIGN_12 = '-';

prize_ABS_01 = abs(prize_01);
prize_ABS_02 = abs(prize_02);
prize_ABS_03 = abs(prize_03);
prize_ABS_04 = abs(prize_04);
prize_ABS_05 = abs(prize_05);
prize_ABS_06 = abs(prize_06);
prize_ABS_07 = abs(prize_07);
prize_ABS_08 = abs(prize_08);
prize_ABS_09 = abs(prize_09);
prize_ABS_10 = abs(prize_10);
prize_ABS_11 = abs(prize_11);
prize_ABS_12 = abs(prize_12);

prize_STRING_01 = cats(SIGN_01, vvalue(prize_ABS_01));
prize_STRING_02 = cats(SIGN_02, vvalue(prize_ABS_02));
prize_STRING_03 = cats(SIGN_03, vvalue(prize_ABS_03));
prize_STRING_04 = cats(SIGN_04, vvalue(prize_ABS_04));
prize_STRING_05 = cats(SIGN_05, vvalue(prize_ABS_05));
prize_STRING_06 = cats(SIGN_06, vvalue(prize_ABS_06));
prize_STRING_07 = cats(SIGN_07, vvalue(prize_ABS_07));
prize_STRING_08 = cats(SIGN_08, vvalue(prize_ABS_08));
prize_STRING_09 = cats(SIGN_09, vvalue(prize_ABS_09));
prize_STRING_10 = cats(SIGN_10, vvalue(prize_ABS_10));
prize_STRING_11 = cats(SIGN_11, vvalue(prize_ABS_11));
prize_STRING_12 = cats(SIGN_12, vvalue(prize_ABS_12));
run;

任何建议如何处理？在此先感谢您的帮助 ：））

标签： sqlsasformattingnumericenterprise-guide

Answer 1

首先 - 您不需要为此使用字符串，您可以使用格式来完成这一切 - 但这是我将如何做您正在做的事情。

1. 使用数组。请不要将这段代码写 12 次，它容易出错且浪费。
2. 简化。如果您总是希望总和为 0，否则会丢失，请在总和中包含 0！使用ifcwhich 是三元 if - 基本上，如果它是 true 返回第二个参数，如果 false 返回第三个参数。
3. 你可以round到正确的地方值，然后put。

这是示例代码：

data old;
  call streaminit(7);
  array effect[12] effect_2021_01-effect_2021_12;
  array effect_t[12] effect_trash_t_2021_01-effect_trash_t_2021_12;
  array effect_c[12] effect_trash_c_2021_01-effect_trash_c_2021_12;
  do _n_ = 1 to 10;
    do i = 1 to dim(effect);
      effect[i] = rand('Uniform')*100;
      effect_T[i] = rand('Uniform')*100;
      effect_C[i] = rand('Uniform')*100;
    end;
    output;
  end;
run;

data new;
  set old;
  length prize_string_01-prize_String_12 $24;
  array prize[12] prize_01-prize_12;
  array prize_str[12] $ prize_string_01-prize_string_12;
  array effect[12] effect_2021_:;
  array effect_t[12] effect_trash_t_2021_:;
  array effect_c[12] effect_trash_c_2021_:;

  do i = 1 to dim(prize);
    prize[i] = sum(effect[i],effect_t[i],effect_c[i],0);
    sign = ifc(prize[i] ge 0, '+', '-');
    prize_str[i] = cats(sign,put(abs(round(prize[i],.01)),12.3));
  end;
run;

但是-也许我会使用图片格式，尽管我不确定您是否可以在其中获得尾随零；但你可能可以。这是一个的开始，无论如何，这至少是前缀......

proc format;
  picture twodecf(round )
    low-<0 = '0000.99' ( prefix='-' )
    0-high = '0000.99' ( prefix='+' )
    ;
quit;

然后可以将其应用于该值，并且可以将 CATS 设为零，至少自动为您应用符号，或者也许有人可以想出如何添加额外的零。