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,988

Re: Update Blank Rows with Value of Previous ROW

Posted in reply to Kody_devl

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,988

Re: Update Blank Rows with Value of Previous ROW

Posted in reply to Kody_devl

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: 11,343

Re: Update Blank Rows with Value of Previous ROW

Posted in reply to Kody_devl

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
  • 180 views
  • 1 like
  • 3 in conversation