BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Solly7
Pyrite | Level 9


I have data i with columns:status(active or inactive),yearmonth(202301..etc) and acct_no so i need to execute below logic in sas:


• StatusNew = Active if current yearmonth status=active, AND was also active in the previous month
• Status New= Re-activated if current yearmonth status=active is  AND was inactive in the previous yearmonth.
If account did not exist in the previous month, then just flag them as inactive or active based on their current month’s status. 

Issue that i am having with my output from below code is that StatusNew for acct_no =2 in 202302 is Re-activated but it should be active based on above rules

 

 

data transactions;
  input yearmonth acct_no $ status $;
  datalines;
202301 1 inactive
202301 2 active
202302 1 inactive
202302 2 active
202302 3 inactive
202303 3 active
202302 4 active
;

proc sort data=transactions;
  by acct_no  yearmonth;
run;

data results;
  set transactions;
  by acct_no;
  length StatusNew $12;

  retain statusnew ' ';


  if first.acct_no then do;
    statusnew = status;
  end;
  else do;
  
  if lag(status)="active" and status = "active" then
    StatusNew = "Active";
  else if lag(status)="inactive" and status = "active" then
    StatusNew = "Re-activated";
  else
    StatusNew = status;
  end;

  if last.acct_no then output;

  drop lag_status;
run;

  
1 ACCEPTED SOLUTION

Accepted Solutions
JosvanderVelden
SAS Super FREQ

Help is not a very descriptive subject. Please on subsequent posts use a better subject so that readers get a good idea on what you want help with.

 

I think the issue is a failure in the code that implements your rule for the first obs in the by group. In your code on the first.acct_no you only set statusnew. I believe you also need to do something with the lag(status) because otherwise the lag(status) is the status of last.acc_no.

Give the code below a try to verify if that solves your rule logic issue.

data results;
   set transactions;
   by acct_no;
   length statusnew $12;
   retain statusnew ' ';
   lagstatus = lag(status);
   if first.acct_no then do;
      statusnew = status;
      lagstatus = " ";
      end;
   else do;  
      if lagstatus = "active" and status = "active" then statusnew = "Active";
      else if lagstatus = "inactive" and status = "active" then statusnew = "Re-activated";
           else statusnew = status;
   end;
   if last.acct_no then output;
   drop lagstatus;
run;

 

View solution in original post

3 REPLIES 3
JosvanderVelden
SAS Super FREQ

Help is not a very descriptive subject. Please on subsequent posts use a better subject so that readers get a good idea on what you want help with.

 

I think the issue is a failure in the code that implements your rule for the first obs in the by group. In your code on the first.acct_no you only set statusnew. I believe you also need to do something with the lag(status) because otherwise the lag(status) is the status of last.acc_no.

Give the code below a try to verify if that solves your rule logic issue.

data results;
   set transactions;
   by acct_no;
   length statusnew $12;
   retain statusnew ' ';
   lagstatus = lag(status);
   if first.acct_no then do;
      statusnew = status;
      lagstatus = " ";
      end;
   else do;  
      if lagstatus = "active" and status = "active" then statusnew = "Active";
      else if lagstatus = "inactive" and status = "active" then statusnew = "Re-activated";
           else statusnew = status;
   end;
   if last.acct_no then output;
   drop lagstatus;
run;

 

PaigeMiller
Diamond | Level 26

Please go back to your ORIGINAL message and provide a meaningful subject line that briefly describes the problem. Help is not meaningful (in every thread someone needs help) and does not describe the problem.

--
Paige Miller
Ksharp
Super User
data transactions;
  input yearmonth :yymmn6. acct_no $ status $;
  format yearmonth yymmn6.;
  datalines;
202301 1 inactive
202301 2 active
202302 1 inactive
202302 2 active
202302 3 inactive
202303 3 active
202302 4 active
;

data want;
 if _n_=1 then do;
   if 0 then set transactions(rename=(status=_status));
   declare hash h(dataset:'transactions(rename=(status=_status))');
   h.definekey('yearmonth','acct_no');
   h.definedata('_status');
   h.definedone();
 end;
set transactions;
length StatusNew $ 40;
StatusNew=status;
prev_yearmonth=intnx('month',yearmonth,-1);
if h.find(key:prev_yearmonth,key:acct_no)=0 then do;
 if status='active' and _status='active' then StatusNew='active    ';
 if status='active' and _status='inactive' then StatusNew='re-active';
end;
drop _status prev_yearmonth;
run;

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 765 views
  • 2 likes
  • 4 in conversation