I would like to get the previous values populated into newly created variables prev_actual and prev_predict. However, I want the values to reset to missing at the beginning of the by group.
This is my code:
data have;
set sashelp.prdsale;
run;
proc sort data = have;
by country region division prodtype product year month;
run;
data want;
set have;
by country region division prodtype product year month;
if first.month then do;
prev_actual = lag(actual);
prev_predict = lag(predict);
end;
run;
This is a part of the result I get, the change in product should result in missing prev_actual & missing prev_predict but the values from the previous product is populated:
ACTUAL PREDICT COUNTRY REGION DIVISION PRODTYPE PRODUCT QUARTER YEAR MONTH prev_actual prev_predict
$387.00 $348.00 CANADA EAST CONSUMER FURNITURE BED 3 1994 Aug 490 311
$688.00 $458.00 CANADA EAST CONSUMER FURNITURE BED 3 1994 Sep 387 348
$650.00 $195.00 CANADA EAST CONSUMER FURNITURE BED 4 1994 Oct 688 458
$447.00 $658.00 CANADA EAST CONSUMER FURNITURE BED 4 1994 Nov 650 195
$91.00 $704.00 CANADA EAST CONSUMER FURNITURE BED 4 1994 Dec 447 658
$5.00 $425.00 CANADA EAST CONSUMER FURNITURE SOFA 1 1993 Jan 91 704
$164.00 $215.00 CANADA EAST CONSUMER FURNITURE SOFA 1 1993 Feb 5 425
$422.00 $948.00 CANADA EAST CONSUMER FURNITURE SOFA 1 1993 Mar 164 215
$424.00 $544.00 CANADA EAST CONSUMER FURNITURE SOFA 2 1993 Apr 422 948
This row should be missing but it is populated with prev_actual = 71 & prev_predict = 704
ACTUAL PREDICT COUNTRY REGION DIVISION PRODTYPE PRODUCT QUARTER YEAR MONTH prev_actual prev_predict
$5.00 $425.00 CANADA EAST CONSUMER FURNITURE SOFA 1 1993 Jan . .
I would appreciate some help in resolving this.
Thank you.