使用 SAS proc expand 填充缺失值 [英] Use SAS proc expand for filling missing values

问题描述

我有以下问题:

我想用 proc expand 填充缺失值，只需从下一个数据行中获取值即可.

I want to fill missing values with proc expand be simply taking the value from the next data row.

我的数据如下所示:

date;index;
29.Jun09;-1693
30.Jun09;-1692
01.Jul09;-1691
02.Jul09;-1690
03.Jul09;-1689
04.Jul09;.
05.Jul09;.
06.Jul09;-1688
07.Jul09;-1687
08.Jul09;-1686
09.Jul09;-1685
10.Jul09;-1684
11.Jul09;.
12.Jul09;.
13.Jul09;-1683

如您所见，某些日期缺少索引.我想实现以下目标:

As you can see for some dates the index is missing. I want to achieve the following:

date;index;
29.Jun09;-1693
30.Jun09;-1692
01.Jul09;-1691
02.Jul09;-1690
03.Jul09;-1689
04.Jul09;-1688
05.Jul09;-1688
06.Jul09;-1688
07.Jul09;-1687
08.Jul09;-1686
09.Jul09;-1685
10.Jul09;-1684
11.Jul09;-1683
12.Jul09;-1683
13.Jul09;-1683

如您所见，缺失数据的值取自下一行(11.Jul09 和 12Jul09 的值来自 13Jul09)

As you can see the values for the missing data where taken from the next row (11.Jul09 and 12Jul09 got the value from 13Jul09)

因此 proc expand 似乎是正确的方法，我开始使用此代码:

So proc expand seems to be the right approach and i started using this code:

PROC EXPAND DATA=DUMMY
OUT=WORK.DUMMY_TS
FROM = DAY
ALIGN = BEGINNING
METHOD = STEP
OBSERVED = (BEGINNING, BEGINNING);

ID date;
CONVERT index /;
RUN;
QUIT;

这填补了空白，但从前一行以及我为 ALIGN、OBSERVED 设置的任何内容，甚至对数据进行降序排序，我都无法实现我想要的行为.

This filled the gaps but from the previous row and whatever I set for ALIGN, OBSERVED or even sorting the data descending I do not achieve the behavior I want.

如果你知道如何使它正确，如果你能给我一个提示，那就太好了.关于 proc expand 的好论文也很受欢迎.

If you know how to make it right it would be great if you could give me a hint. Good papers on proc expand are apprechiated as well.

感谢您的帮助和亲切的问候斯蒂芬

Thanks for your help and kind regards Stephan

推荐答案

我不知道 proc expand.但显然这可以通过几个步骤来完成.

I don't know about proc expand. But apparently this can be done with a few steps.

读取数据集并创建一个将获得 n 值的新变量.

Read the dataset and create a new variable that will get the value of n.

data have;
    set have;
    pos = _n_;
run;

按此新变量对数据集进行降序排序.

Sort this dataset by this new variable, in descending order.

proc sort data=have;
    by descending pos;
run;

使用Lag或retain从下"行开始填充缺失值(排序后顺序颠倒).

Use Lag or retain to fill the missing values from the "next" row (After sorting, the order will be reversed).

data want;
    set have (rename=(index=index_old));
    retain index;
    if not missing(index_old) then index = index_old;
run;

如果需要重新排序.

proc sort data=want;
    by pos;
run;

这篇关于使用 SAS proc expand 填充缺失值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！