topic Re: Help in SAS Programming

Help

Solly7 — Fri, 28 Jul 2023 08:43:38 GMT

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;

Re: Help

JosvanderVelden — Fri, 28 Jul 2023 09:49:56 GMT

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;

Re: Help

PaigeMiller — Fri, 28 Jul 2023 10:00:03 GMT

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.

Re: Help

Ksharp — Fri, 28 Jul 2023 11:38:59 GMT

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;