I have table in SAS Enterprise Guide like below. Table presents history of clients and his eventually changes of services used (columns: BEFORE, AGAIN).

ID and DT are not sorted, but could be if it is important

data types of columns:

• ID - numeric
• DT - date
• BEFORE - character
• AFTER - character

And I need to create new binary column "COL1" with values 0/1

• If some client ("ID") changed service from PR (in "BEFORE") to P or M (in "AFTER") and did not return to PR (in "AFTER") service for another 4 or more months ("DT") --> then 1
• If some client changed service from PR (in "BEFORE") to P or M (in "AFTER") and return to PR (in "AFTER") earlier than after 4 months --> then 0
• If client did not make change from PR to P or M --> then 0

So as a result I need something like below:

I totally do not know how to do that in PROC SQL in SAS Enterprise Guide, do you know what to do ?

CodePudding user response：

You probably just need to remember the last date that it moved from PR. You need to process ALL of the records for an ID to figure out the flag value.
You could use a DOW loop for this.

data want;
do until (last.id);
  set have;
  by id dt;
  if (before='PR' and after in ('M' 'P')) then from_dt=dt;
  if after='PR' and intnx('month',dt,-4,'s') <= from_dt then from_dt=.;
  if not missing(from_dt) then do;
     if dt > intnx('month',from_dt,4,'s') then flag=1;
  end;
end;
  flag=sum(flag,0);
  format from_dt date9.;
  keep id flag;
run;

If you want to merge it back onto the detailed records then use a double DOW loop where the second loop is just to re-read the observations so they can be written out with the FLAG variable set.

data want;
do until (last.id);
  set have;
  by id dt;
  if (before='PR' and after in ('M' 'P')) then from_dt=dt;
  if after='PR' and intnx('month',dt,-4,'s') <= from_dt then from_dt=.;
  if not missing(from_dt) then do;
     if dt > intnx('month',from_dt,4,'s') then flag=1;
  end;
end;
  flag=sum(flag,0);
do until (last.id);
  set have;
  by id ;
  output;
end;
  format from_dt date9.;
  drop from_dt;
run;

Result

Obs     ID           DT    BEFORE    AFTER      from_dt    flag

 1      11    25JUN2021      P        P               .      0
 2      85    01AUG2021      PR       PR              .      0
 3      85    15AUG2021      PR       M               .      0
 4      85    22AUG2021      M        PR              .      0
 5     122    22JUL2021      PR       PR              .      0
 6     123    21MAY2021      PR       P       21MAY2021      1
 7     123    28OCT2021      P        P       21MAY2021      1
 8     123    30OCT2023      P        P       21MAY2021      1