Help

[Solly7]

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;

  

[JosvanderVelden · Accepted solution]

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]

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.

[Ksharp]

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;