BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
NewSASPerson
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

@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;

 

--
Paige Miller

View solution in original post

2 REPLIES 2
PaigeMiller
Diamond | Level 26

@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;

 

--
Paige Miller
NewSASPerson
Quartz | Level 8

Perfect! Thank you, That worked

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 519 views
  • 1 like
  • 2 in conversation