用之前的非空白值填充变量的空白值 SAS 9.3 [英] Fill the blank values of a variable with the previous non blank value SAS 9.3

问题描述

我使用的数据集类似于:

I'm using a dataset which is something like :

+----------+--------+-------+
| Variable | Level  | Value |
+----------+--------+-------+
| sexe     | men    |    10 |
|          | female |    20 |
| age      | 0-20   |     5 |
|          | 20-40  |     5 |
|          | 40-60  |    10 |
|          | >60    |    10 |
+----------+--------+-------+

并且我想使用之前的非空白单元格来填充空白"单元格以获得类似的东西.

And I would like to fulfill the "blank" cells using the previous non-blank cell to obtain something like this.

+----------+--------+-------+
| Variable | Level  | Value |
+----------+--------+-------+
| sexe     | men    |    10 |
| sexe     | female |    20 |
| age      | 0-20   |     5 |
| age      | 20-40  |     5 |
| age      | 40-60  |    10 |
| age      | >60    |    10 |
+----------+--------+-------+

我在 DATA 步骤中尝试了各种可能性，主要使用 LAG() 函数.这个想法是在单元格为空时读取前一行并填充它.

I tried various possibilities in DATA step mostly with the LAG() function. The idea was to read the previous row when the cell was empty and fill with that.

DATA test;
   SET test;

   IF variable = . THEN DO;
      variable = LAG1(variable);
   END;
RUN;

我得到了

+----------+--------+-------+
| Variable | Level  | Value |
+----------+--------+-------+
|          | men    |    10 |
| sexe     | female |    20 |
|          | 0-20   |     5 |
| age      | 20-40  |     5 |
|          | 40-60  |    10 |
|          | >60    |    10 |
+----------+--------+-------+

问题是好的字符串并不总是只在上面一行.但我不明白为什么 SAS 在第一行和 3d 行中放置空白.它不必修改这一行，因为我说的是If variable = .".我知道如何在 Python 或 R 中使用一些 for 循环执行此操作，但在 SAS 中我没有找到好的解决方案.

The problem was the good string is not always just one row upper. But I don't understand why SAS put blank in the first and 3d line. It didn't have to modify this line because I said "If variable = .". I know how to do this in Python or in R with some for loop but I didn't find good solution in SAS.

我试图将字符串放入一个带有CALL SYMPUT" 以及 "RETAIN" 但它也不起作用.

I tried to put the string inside a variable with "CALL SYMPUT" and also with "RETAIN" but it didn't work too.

必须有一种简单而优雅的方法来做到这一点.任何想法?

There must be a simple and elegant way to do this. Any idea?

推荐答案

您不能在 IF 中使用 LAG 并获得该结果 - LAG 实际上并不像您想象的那样工作.RETAIN 是我想说的正确方式:

You can't use LAG inside an IF and get that result - LAG doesn't actually work the way you think. RETAIN is the correct way I'd say:

DATA test;
   SET test;
   retain _variable;
   if not missing(variable) then _variable=variable;
   else variable=_variable;
   drop _variable;
RUN;

Lag 实际上并没有转到上一条记录并获取其值；它的作用是建立一个队列，每次调用 LAG 时，它都会从前面取出一条记录，并在后面添加一条记录.这意味着如果 LAG 位于条件块内，则它不会针对 false 条件执行，并且您不会获得队列.您可以使用 IFN 和 IFC 函数，无论布尔值如何，它们都会评估 true 和 false 条件，但在这种情况下，RETAIN 可能更容易.

Lag doesn't actually go to the previous record and get its value; what it does is set up a queue, and each time LAG is called it takes off a record from the front and adds a record to the back. This means that if LAG is inside a conditional block, it won't execute for the false condition, and you don't get your queue. You can use IFN and IFC functions, which evaluate both true and false conditions regardless of the boolean, but in this case RETAIN is probably easier.

这篇关于用之前的非空白值填充变量的空白值 SAS 9.3的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！