How to create 0/1 flag witch infrom whether values were changed between 2 column in some dates in PROC SQL in SAS Enterprise Guide?

ritendro 0
Viewed 43

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
ID DT BEFORE AFTER
123 21MAY2021 PR P
123 28OCT2021 P P
123 30OCT2023 P P
85 01AUG2021 PR PR
85 15AUG2021 PR M
85 22AUG2021 M PR
11 25JUN2021 P P
122 22JUL2021 PR PR
444 18MAY2022 PR M

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:

ID DT BEFORE AFTER COL1
123 21MAY2021 PR P 1 --> changed PR to P and did not back to PR for 4 or more months
123 28OCT2021 P P 1
123 30OCT2023 P P 1
85 01AUG2021 PR PR 0
85 15AUG2021 PR M 0
85 22AUG2021 M PR 0 --> back to PR less than 4 months after changing PR to P or M
11 25JUN2021 P P 0 --> client did not make change from PR to P or M
122 22JUL2021 PR PR 0 --> client did not make change from PR to P or M
444 18MAY2022 PR M 1 --> customer changed PR to P or M and we have no further history of changes so 1

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

enterprise-guide proc-sql sas date sql
1 Answers

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
user7NPk0
Related