sql - SAS 或 Postgresql :add column value according to another column value

Question

我想添加一个“索引”列，当“变量”列中的行具有相同值时，“索引”列的值相同。您可以使用 Postgresql 或 SAS 语法。

一件事是“变量”列中的值每天都在变化，例如表A和表B，因此硬编码是 Not Acceptable 。任何建议都将受到赞赏!

+----------+--------------+------+-------+-----+-----+-------+
| variable | new_variable | type | start | end | woe | index |
+----------+--------------+------+-------+-----+-----+-------+
| A        | mi_A         | char | 1     |     | 1.3 |     1 |
| A        | mi_A         | char | 0     |     | 0.6 |     1 |
| B        | mi_B         | char | 1     |     | 5.4 |     2 |
| B        | mi_B         | char | 0     |     | 0.1 |     2 |
| gnd_cd   | gnd_cd       | char | 3     |     | 1.3 |     3 |
| gnd_cd   | gnd_cd       | char | @0    |     | 0.6 |     3 |
| gnd_cd   | gnd_cd       | char | 2     |     | 5.4 |     3 |
| gnd_cd   | gnd_cd       | char | N     |     | 0.1 |     3 |
| gnd_cd   | gnd_cd       | char | 1     |     | 1.3 |     3 |
| gnd_cd   | gnd_cd       | char | 99    |     | 0.6 |     3 |
| mar_sign | mar_sign     | char | 0     |     | 5.4 |     4 |
| mar_sign | mar_sign     | char | Y     |     | 0.1 |     4 |
| mar_sign | mar_sign     | char | N     |     |   6 |     4 |
| C        | C            | char | 6     |     |   2 |     5 |
| C        | C            | char | 7     |     | 2.1 |     5 |
| C        | C            | char | 8     |     | 2.2 |     5 |
+----------+--------------+------+-------+-----+-----+-------+
                         (tableA)

+--------------+--------------+------+-------+-----+-----+-------+
|   variable   | new_variable | type | start | end | woe | index |
+--------------+--------------+------+-------+-----+-----+-------+
| D            | mi_D         | char | 1     |     |   1 |     1 |
| D            | mi_D         | char | 0     |     |   2 |     1 |
| E            | mi_E         | char | 1     |     |   2 |     2 |
| E            | mi_E         | char | 0     |     |   3 |     2 |
| education_bg | education_bg | char | 3     |     |   1 |     3 |
| education_bg | education_bg | char | @0    |     |   5 |     3 |
| education_bg | education_bg | char | 2     |     |   6 |     3 |
| education_bg | education_bg | char | N     |     |   4 |     3 |
| education_bg | education_bg | char | 1     |     |   3 |     3 |
| education_bg | education_bg | char | 99    |     |   3 |     3 |
| sex          | sex          | char | 0     |     |   2 |     4 |
| sex          | sex          | char | Y     |     |   1 |     4 |
| sex          | sex          | char | N     |     |   0 |     4 |
| C            | C            | char | 6     |     |   6 |     5 |
| C            | C            | char | 7     |     |   4 |     5 |
| C            | C            | char | 8     |     |   1 |     5 |
+--------------+--------------+------+-------+-----+-----+-------+
                             (tableB)

Answer 1

您可以在 SAS 中使用保留功能并按变量分组在单个数据步中执行此操作。

代码:

data have;
infile datalines dlm='|';
input variable $ new_variable $ type $ start $  end $ woe ;
datalines;
| A        | mi_A         | char | 1     |     | 1.3 
| A        | mi_A         | char | 0     |     | 0.6 
| B        | mi_B         | char | 1     |     | 5.4 
| B        | mi_B         | char | 0     |     | 0.1 
| gnd_cd   | gnd_cd       | char | 3     |     | 1.3 
| gnd_cd   | gnd_cd       | char | @0    |     | 0.6 
| gnd_cd   | gnd_cd       | char | 2     |     | 5.4 
| gnd_cd   | gnd_cd       | char | N     |     | 0.1 
| gnd_cd   | gnd_cd       | char | 1     |     | 1.3 
| gnd_cd   | gnd_cd       | char | 99    |     | 0.6 
| mar_sign | mar_sign     | char | 0     |     | 5.4 
| mar_sign | mar_sign     | char | Y     |     | 0.1 
| mar_sign | mar_sign     | char | N     |     |   6 
| C        | C            | char | 6     |     |   2 
| C        | C            | char | 7     |     | 2.1 
| C        | C            | char | 8     |     | 2.2  
run;

data want;
set have ;
by variable notsorted;
retain index;
if first.variable then index+1;
run;

注意:我创建了索引并且只用新的组值增加它的值。