如何在SAS中为字符串变量id生成数值编码变量id_n

需求说明 #

需要在SAS中创建数值指示变量id_n，用于唯一标识字符串变量id的不同取值水平。由于id的取值会动态变化，无法通过固定的if-then逻辑实现，需要通用的自动映射方法。

示例数据代码 #

data value_id;
length id $54 ;
informat id $54. ; 
input id $ ;
cards ;
9jdbh2e7z8-dc4o8mgsft-qi778mt7s0-rz20vk4xwo-ybcx8gaiy8
tsknifwb29-818zgpj2be-vq7558xhqa-1lgqck7219-rq1ojedtmp
ts1j2y9q6u-nghpfhdxsl-vkdwk060gg-s3tred6a7g-h5iqsl8cir
jg1qpqhofy-02d2m62ayb-fg2f6dtvqc-vx4lsnowcj-s4kg37wxah
o3qadvrqtl-kdyw9qpfir-7xeilvuk7e-g73olb67tm-nwwvla6r4g
gc6dny3d5n-qzdkgfkpoc-iv1vnmwu4d-hubjun73y1-mbaggyrmkq
dbvdcbvafv-cmb2zp67tn-gpnfwcvvpt-nl8qpgwn8b-l3biox4318
byxl352kpd-se5godm0gv-jukpzv1u7x-8kffj5th80-mf04nzwvrf
98llcibqon-u86ww0mzgo-ut58htcfv1-lybgj2gsn2-zlvu6n0mym
wfrbcr3i8e-3vodo5wkrr-hp733zwkhy-uxm9uf16zp-5y11re5um5
9jdbh2e7z8-dc4o8mgsft-qi778mt7s0-rz20vk4xwo-ybcx8gaiy8
tsknifwb29-818zgpj2be-vq7558xhqa-1lgqck7219-rq1ojedtmp
ts1j2y9q6u-nghpfhdxsl-vkdwk060gg-s3tred6a7g-h5iqsl8cir
jg1qpqhofy-02d2m62ayb-fg2f6dtvqc-vx4lsnowcj-s4kg37wxah
o3qadvrqtl-kdyw9qpfir-7xeilvuk7e-g73olb67tm-nwwvla6r4g
gc6dny3d5n-qzdkgfkpoc-iv1vnmwu4d-hubjun73y1-mbaggyrmkq
dbvdcbvafv-cmb2zp67tn-gpnfwcvvpt-nl8qpgwn8b-l3biox4318
byxl352kpd-se5godm0gv-jukpzv1u7x-8kffj5th80-mf04nzwvrf
98llcibqon-u86ww0mzgo-ut58htcfv1-lybgj2gsn2-zlvu6n0mym
wfrbcr3i8e-3vodo5wkrr-hp733zwkhy-uxm9uf16zp-5y11re5um5
9jdbh2e7z8-dc4o8mgsft-qi778mt7s0-rz20vk4xwo-ybcx8gaiy8
tsknifwb29-818zgpj2be-vq7558xhqa-1lgqck7219-rq1ojedtmp
ts1j2y9q6u-nghpfhdxsl-vkdwk060gg-s3tred6a7g-h5iqsl8cir
jg1qpqhofy-02d2m62ayb-fg2f6dtvqc-vx4lsnowcj-s4kg37wxah
o3qadvrqtl-kdyw9qpfir-7xeilvuk7e-g73olb67tm-nwwvla6r4g
gc6dny3d5n-qzdkgfkpoc-iv1vnmwu4d-hubjun73y1-mbaggyrmkq
dbvdcbvafv-cmb2zp67tn-gpnfwcvvpt-nl8qpgwn8b-l3biox4318
byxl352kpd-se5godm0gv-jukpzv1u7x-8kffj5th80-mf04nzwvrf
98llcibqon-u86ww0mzgo-ut58htcfv1-lybgj2gsn2-zlvu6n0mym
wfrbcr3i8e-3vodo5wkrr-hp733zwkhy-uxm9uf16zp-5y11re5um5
9jdbh2e7z8-dc4o8mgsft-qi778mt7s0-rz20vk4xwo-ybcx8gaiy8
tsknifwb29-818zgpj2be-vq7558xhqa-1lgqck7219-rq1ojedtmp
ts1j2y9q6u-nghpfhdxsl-vkdwk060gg-s3tred6a7g-h5iqsl8cir
jg1qpqhofy-02d2m62ayb-fg2f6dtvqc-vx4lsnowcj-s4kg37wxah
o3qadvrqtl-kdyw9qpfir-7xeilvuk7e-g73olb67tm-nwwvla6r4g
gc6dny3d5n-qzdkgfkpoc-iv1vnmwu4d-hubjun73y1-mbaggyrmkq
dbvdcbvafv-cmb2zp67tn-gpnfwcvvpt-nl8qpgwn8b-l3biox4318
byxl352kpd-se5godm0gv-jukpzv1u7x-8kffj5th80-mf04nzwvrf
98llcibqon-u86ww0mzgo-ut58htcfv1-lybgj2gsn2-zlvu6n0mym
wfrbcr3i8e-3vodo5wkrr-hp733zwkhy-uxm9uf16zp-5y11re5um5
;

proc sort data = value_id
out = value_id_s  ascii  ;
by id ;
run; 

期望输出 #

生成数值变量id_n，同一id取值对应相同的id_n，不同取值对应连续的数值标识，示例如下：

id      id_n
98llcibqon-u86ww0mzgo-ut58htcfv1-lybgj2gsn2-zlvu6n0mym         1
98llcibqon-u86ww0mzgo-ut58htcfv1-lybgj2gsn2-zlvu6n0mym         1
98llcibqon-u86ww0mzgo-ut58htcfv1-lybgj2gsn2-zlvu6n0mym         1
98llcibqon-u86ww0mzgo-ut58htcfv1-lybgj2gsn2-zlvu6n0mym         1
9jdbh2e7z8-dc4o8mgsft-qi778mt7s0-rz20vk4xwo-ybcx8gaiy8         2
9jdbh2e7z8-dc4o8mgsft-qi778mt7s0-rz20vk4xwo-ybcx8gaiy8         2
9jdbh2e7z8-dc4o8mgsft-qi778mt7s0-rz20vk4xwo-ybcx8gaiy8         2
9jdbh2e7z8-dc4o8mgsft-qi778mt7s0-rz20vk4xwo-ybcx8gaiy8         2
byxl352kpd-se5godm0gv-jukpzv1u7x-8kffj5th80-mf04nzwvrf         3
byxl352kpd-se5godm0gv-jukpzv1u7x-8kffj5th80-mf04nzwvrf         3
byxl352kpd-se5godm0gv-jukpzv1u7x-8kffj5th80-mf04nzwvrf         3
byxl352kpd-se5godm0gv-jukpzv1u7x-8kffj5th80-mf04nzwvrf         3
...                                                          ...

Stata中的实现方式 #

在Stata中只需一行命令即可完成：

encode id, gen(id_n)

执行后通过tab1 id id_n, nolabel可验证结果：

-> tabulation of id  

                                     id |      Freq.     Percent        Cum.
----------------------------------------+-----------------------------------
98llcibqon-u86ww0mzgo-ut58htcfv1-lybg.. |          4       10.00       10.00
9jdbh2e7z8-dc4o8mgsft-qi778mt7s0-rz20.. |          4       10.00       20.00
byxl352kpd-se5godm0gv-jukpzv1u7x-8kff.. |          4       10.00       30.00
dbvdcbvafv-cmb2zp67tn-gpnfwcvvpt-nl8q.. |          4       10.00       40.00
gc6dny3d5n-qzdkgfkpoc-iv1vnmwu4d-hubj.. |          4       10.00       50.00
jg1qpqhofy-02d2m62ayb-fg2f6dtvqc-vx4l.. |          4       10.00       60.00
o3qadvrqtl-kdyw9qpfir-7xeilvuk7e-g73o.. |          4       10.00       70.00
ts1j2y9q6u-nghpfhdxsl-vkdwk060gg-s3tr.. |          4       10.00       80.00
tsknifwb29-818zgpj2be-vq7558xhqa-1lgq.. |          4       10.00       90.00
wfrbcr3i8e-3vodo5wkrr-hp733zwkhy-uxm9.. |          4       10.00      100.00
----------------------------------------+-----------------------------------
                                  Total |         40      100.00

-> tabulation of id_n  

       id_n |      Freq.     Percent        Cum.
------------+-----------------------------------
          1 |          4       10.00       10.00
          2 |          4       10.00       20.00
          3 |          4       10.00       30.00
          4 |          4       10.00       40.00
          5 |          4       10.00       50.00
          6 |          4       10.00       60.00
          7 |          4       10.00       70.00
          8 |          4       10.00       80.00
          9 |          4       10.00       90.00
         10 |          4       10.00      100.00
------------+-----------------------------------
      Total |         40      100.00

SAS中的解决方案 #

方法1：排序后数据步动态生成标识 #

基于已排序的value_id_s数据集，通过分组判断自动递增标识：

data value_id_final;
    set value_id_s;
    by id;
    retain id_n 0;
    if first.id then id_n + 1;
run;

• by id：开启分组处理，自动生成first.id（每组第一个观测标识）和last.id变量
• retain id_n 0：保留id_n的取值，避免每次迭代重置为缺失
• if first.id then id_n + 1：遇到新分组时，id_n加1

方法2：PROC FORMAT建立映射后赋值 #

先提取唯一id生成自定义格式，再通过格式转换生成数值标识：

/* 提取唯一id并生成格式参数 */
proc sql noprint;
    select distinct id, monotonic()
    into :id_list separated by ' ', :num_list separated by ' '
    from value_id;
quit;

/* 创建字符串到数值的映射格式 */
proc format;
    value $idmap
        &id_list = &num_list
    ;
run;

/* 生成id_n变量 */
data value_id_final;
    set value_id;
    id_n = input(put(id, $idmap.), best.);
run;

• monotonic()：生成连续的数值标识
• 自定义格式$idmap建立字符串与数值的一一映射
• 通过put（格式转换）和input（类型转换）得到数值变量id_n

方法3：PROC RANK直接生成排名标识 #

利用PROC RANK对字符串变量进行排名，快速生成数值标识：

proc rank data=value_id out=value_id_final ties=low;
    var id;
    ranks id_n;
run;

/* 若需要id_n从1开始，执行以下步骤 */
data value_id_final;
    set value_id_final;
    id_n = id_n + 1;
run;

• ties=low：相同id取值赋予相同的最小排名值
• 默认生成的id_n从0开始，加1后可得到从1开始的连续标识

内容的提问来源于stack exchange，提问作者blue and grey