Populate Variables with Previous Values

NewSASPerson — Thu, 16 Jan 2020 15:38:21 GMT

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.

Re: Populate Variables with Previous Values

PaigeMiller — Thu, 16 Jan 2020 15:38:43 GMT

@NewSASPerson wrote:

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.

So does this mean you want a missing at the beginning of Country or the beginning of Region or the beginning of Division or ... etc. ... ?

Let's assume it means each Product — this ought to have a missing value for the first record of each product.

data want;
	set have;
	by country region division prodtype product year month;
	prev_actual = lag(actual);
	prev_predict = lag(predict);
	if first.product then do;
            prev_actual=.;
            prev_predict=.;
	end;
run;

Re: Populate Variables with Previous Values

NewSASPerson — Thu, 16 Jan 2020 15:39:52 GMT

Perfect! Thank you, That worked

topic Populate Variables with Previous Values in New SAS User

Populate Variables with Previous Values

Re: Populate Variables with Previous Values

Re: Populate Variables with Previous Values