DATA Step, Macro, Functions and more

Update Blank Rows with Value of Previous ROW

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

Update Blank Rows with Value of Previous ROW

How can I update This Table:

Loan_NumberDATELCK_TYPELCK_TYPE_CALC
1001/5/2016 NEW
1001/6/2016  
1001/7/2016  
1001/8/2016  
1001/11/2016  
1001/13/2016 REVIVED
1001/14/2016  
1001/15/2016  
1001/19/2016  
1001/20/2016  
1001/21/2016  
1001/22/2016  
1002/8/2016 

REVIVED

 

 

So that it will look like this:

 

 

Loan_NumberDATELCK_TYPELCK_TYPE_CALC
1001/5/2016NEWNEW
1001/6/2016NEW 
1001/7/2016NEW 
1001/8/2016NEW 
1001/11/2016NEW 
1001/13/2016REVIVEDREVIVED
1001/14/2016REVIVED 
1001/15/2016REVIVED 
1001/19/2016REVIVED 
1001/20/2016REVIVED 
1001/21/2016REVIVED 
1001/22/2016REVIVED 
1002/8/2016REVIVEDREVIVED

 

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;


Accepted Solutions
Solution
‎12-22-2016 08:53 AM
Super User
Super User
Posts: 7,401

Re: Update Blank Rows with Value of Previous ROW

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;

View solution in original post


All Replies
Solution
‎12-22-2016 08:53 AM
Super User
Super User
Posts: 7,401

Re: Update Blank Rows with Value of Previous ROW

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;
Contributor
Posts: 58

Re: Update Blank Rows with Value of Previous ROW

This works perfectly.

 

Thank you

 

 

Super User
Posts: 10,487

Re: Update Blank Rows with Value of Previous ROW

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 171 views
  • 1 like
  • 3 in conversation