topic Update Blank Rows with Value of Previous ROW in SAS Programming

Update Blank Rows with Value of Previous ROW

Kody_devl — Thu, 22 Dec 2016 13:07:45 GMT

How can I update This Table:

Loan_Number	DATE	LCK_TYPE	LCK_TYPE_CALC
100	1/5/2016		NEW
100	1/6/2016
100	1/7/2016
100	1/8/2016
100	1/11/2016
100	1/13/2016		REVIVED
100	1/14/2016
100	1/15/2016
100	1/19/2016
100	1/20/2016
100	1/21/2016
100	1/22/2016
100	2/8/2016		REVIVED

So that it will look like this:

Loan_Number	DATE	LCK_TYPE	LCK_TYPE_CALC
100	1/5/2016	NEW	NEW
100	1/6/2016	NEW
100	1/7/2016	NEW
100	1/8/2016	NEW
100	1/11/2016	NEW
100	1/13/2016	REVIVED	REVIVED
100	1/14/2016	REVIVED
100	1/15/2016	REVIVED
100	1/19/2016	REVIVED
100	1/20/2016	REVIVED
100	1/21/2016	REVIVED
100	1/22/2016	REVIVED
100	2/8/2016	REVIVED	REVIVED

Here is my code which works for the "NOT MISSING" but doesn't works where this LCK_TYPE_CALC is "MISSING"

My Global variable is not working

%global VAR; /* want this variable to hold the last value used - set */

data A_LOAN_DETAIL_E;

set A_LOAN_DETAIL_D;

by Loan_Number Source_Date_dte NOTSORTED;

if Length(LCK_TYPE_CALC) = 0 THEN do; /* Blank doesn't work */

%Let VAR = &VAR.;

end;

if Length(LCK_TYPE_CALC) = 7 THEN do; /* REVIVED works */

%Let VAR = LCK_TYPE_CALC;

end;

if Length(LCK_TYPE_CALC) = 3 THEN do; /* NEW works */

%Let VAR = LCK_TYPE_CALC;

end;

LCK_TYPE = &VAR.;

output;

run;

Re: Update Blank Rows with Value of Previous ROW

RW9 — Thu, 22 Dec 2016 13:17:11 GMT

Well, you would use retain, however if the variable doesn't already exist, then you need to hold that in a separate variable. Good idea to post test data in the form of a datastep so we can post working code (without having to do this and try to guess what your data structure is). So at a guess:

data want;
  length lck_type $50;
  set have (drop=lck_type);
  retain lck_type;
  if not missing(lck_type_calc) then lck_type=lck_type_calc;
run;

Re: Update Blank Rows with Value of Previous ROW

Kody_devl — Thu, 22 Dec 2016 14:18:02 GMT

This works perfectly.

Thank you

Re: Update Blank Rows with Value of Previous ROW

ballardw — Thu, 22 Dec 2016 15:26:35 GMT

May want to make sure that none of your loans are missing the value on the first record encountered. Otherwise there is potential for getting a value from the previous loan.