topic Overriding a value with the previous minimum value with same ID variable in SAS Programming

Overriding a value with the previous minimum value with same ID variable

NW_SAS — Thu, 15 Aug 2019 20:44:24 GMT

I have a data set with 16 quarters of data for each included customer ID and numeric value. I need to see how the minimum value of that numeric value changes overtime. So I need to alter the number so it only changes when there is an new minimum value.

The data currently looks like this:

Customer ID | Date | Numeric Value

ABC123 12/31/17 80

ABC123 3/31/18 90

ABC123 6/30/18 75

ABC123 9/30/18 100

ABC123 12/31/18 110

DEF456 12/31/17 50

DEF456 3/31/18 40

DEF456 6/30/18 55

DEF456 9/30/18 60

DEF456 12/31/18 35

I need it to look like this:

Customer ID | Date | Numeric Value

ABC123 12/31/17 80

ABC123 3/31/18 80

ABC123 6/30/18 75

ABC123 9/30/18 75

ABC123 12/31/18 75

DEF456 12/31/17 50

DEF456 3/31/18 40

DEF456 6/30/18 40

DEF456 9/30/18 40

DEF456 12/31/18 35

I have tried using different lag values with minimal success. Any advise of potential solutions would be appreciated.

Re: Overriding a value with the previous minimum value with same ID variable

r_behata — Thu, 15 Aug 2019 21:10:01 GMT

Data have;
Input Customer_ID $ Date :mmddyy8.  Numeric_Value;
format date mmddyy8.;
cards;
ABC123 12/31/17 80
ABC123 3/31/18 90
ABC123 6/30/18 75
ABC123 9/30/18 100
ABC123 12/31/18 110
DEF456 12/31/17 50
DEF456 3/31/18 40
DEF456 6/30/18 55
DEF456 9/30/18 60
DEF456 12/31/18 35
run;

data want(rename=(Numeric_Value_=Numeric_Value));
	set have;
	by Customer_ID;
	retain Numeric_Value_;

	if first.Customer_ID then
		Numeric_Value_=Numeric_Value;
	else
		do;
			if Numeric_Value < Numeric_Value_ then
				Numeric_Value_=Numeric_Value;
		end;

	drop Numeric_Value;
run;

Re: Overriding a value with the previous minimum value with same ID variable

novinosrin — Thu, 15 Aug 2019 21:20:59 GMT

Same idea but uses MIN function'

Data have;
Input Customer_ID $ Date :mmddyy8.  Numeric_Value;
format date mmddyy8.;
cards;
ABC123 12/31/17 80
ABC123 3/31/18 90
ABC123 6/30/18 75
ABC123 9/30/18 100
ABC123 12/31/18 110
DEF456 12/31/17 50
DEF456 3/31/18 40
DEF456 6/30/18 55
DEF456 9/30/18 60
DEF456 12/31/18 35
run;

data want(rename=(n=Numeric_Value));
do until(last.customer_id);
 set have;
 by customer_id;
 n=min(Numeric_Value , n);
 output;
end;
drop Numeric_Value;
run;