在sas中，每日数据滞后1个月

Question

我有一个SAS中每日数据的数据集。我想把它转换成每月的形式，从前一个月的值按id取差值。例如:

thedate, id, val
2012-01-01, 1, 10
2012-01-01, 2, 14
2012-01-02, 1, 11 
2012-01-02, 2, 12
...
2012-02-01, 1, 20
2012-02-01, 2, 15

我想输出:

thedate, id, val
2012-02-01, 1, 10
2012-02-01, 2, 1

Answer 1

这是一种方法。如果您许可SAS-ETS，那么可能有更好的方法使用PROC EXPAND。

*Setting up the dataset initially;
data have;
informat thedate YYMMDD10.;
input thedate id val;
datalines;
2012-01-01 1 10
2012-01-01 2 14
2012-01-02 1 11 
2012-01-02 2 12
2012-02-01 1 20
2012-02-01 2 15
;;;;
run;
*Sorting by ID and DATE so it is in the right order;
proc sort data=have;
by id thedate;
run;
data want;
  set have;
  retain lastval;  *This is retained from record to record, so the value carries down;
  by id thedate;
  if (first.id) or (last.id) or (day(thedate)=1); *The only records of interest - the first record, the last record, and any record that is the first of a month.;
  * To do END: if (first.id) or (last.id) or (thedate=intnx('MONTH',thedate,0,'E'));
  if first.id then call missing(lastval); *Each time ID changes, reset lastval to missing;
  if missing(lastval) then output; *This will be true for the first record of each ID only - put that record out without changes;
  else do;
    val = val-lastval; *set val to the new value (current value minus retained value);
    output;  *put the record out;
  end;
  lastval=sum(val,lastval); *this value is for the next record;
run;

Answer 2

您可以使用PROC SQL和intnx函数来实现这一点，将上个月的日期提前一个月…

<>之前Proc SQL;创建表延迟选择b.thedate, b.id， (b.val - a.val)作为valFrom mydata b左连接= intnx('month'， ' date '， 1，'s')b.id = a.id按b.date, b.id;退出;之前

这可能需要调整以处理前一个月不存在或与前一个月有不同天数的月份的情况。