保留以替换丢失的数据的 sas 问题 [英] sas issue with retain to replace missing data

问题描述

以下继承的简化代码旨在将列的缺失值替换为组中未缺失条目的值:

The following inherited simplified code is meant to replace missing values of a column with the values of not missing entries in a group:

DATA WORK.TOYDATA;
   INPUT Category $ PRICE;
   DATALINES;
Cat1 2
Cat1 .
Cat1 .
Cat2 .
Cat2 3
Cat2 .
;

DATA WORK.OUTTOYDATA;
SET WORK.TOYDATA;
   BY Category ;
   RETAIN _PRICE;
   IF FIRST.Category THEN _PRICE=PRICE;
   IF NOT MISSING(PRICE) THEN _PRICE=PRICE;
 ELSE PRICE=_PRICE;
 DROP _PRICE;
RUN;

不幸的是，如果缺少组中的第一个条目，这将不起作用.这怎么能解决?

Unfortunately, this will not work if the first entry in a group is missing. How could this be fixed?

推荐答案

当 SAS 逐行处理数据集时，如果第一个值丢失，则没有要替换的值.您可以按类别和价格降序对数据进行排序以规避此问题.

As SAS works row by row through the dataset there is no value to replace if the first value is missing. You could sort the data by Category and Price DESCENDING to circumvent this.

proc sort data= WORK.TOYDATA; by Category DESCENDING PRICE; run; 

或者，如果按类别只有一个非缺失值，您可以使用 sql 连接，例如

Or if there is only one NON-missing value by category you could use a sql join e.g.

proc sql;
create table WORK.OUTTOYDATA as
    select a.Category, coalesce(a.PRICE, b.PRICE) as PRICE
        from WORK.TOYDATA a
            left join   (select distinct Category, PRICE
                            from WORK.TOYDATA 
                                where PRICE ne .
                                ) b
            on a.Category eq b.Category
        ;
quit;

这篇关于保留以替换丢失的数据的 sas 问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！