用以前的非空白值 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 行放置空白.它不必修改这一行，因为我说如果变量 = .".我知道如何在 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 在条件块内，它不会在错误条件下执行，并且您不会得到队列.您可以使用 IFN 和 IFC 函数，无论布尔值如何，它们都会评估真假条件，但在这种情况下，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屋！