Compare Observations and Assign Value to a New Variable.

[sasmaverick]

I have the following dataset:

Match ID                         Comments    Date            Survivor

1                                   Market Only

1                                   Different

1                                   Different

2                                  Market Only

2                                  Different

3                                   Same          10-07-2015

3                                    Same          11-08-2015

3                                    Same          12-09-2015

What I need to do is compare the observations within a given Match ID like follows:

- If there are two 'different' observations within a given match id, then Survivor=' ' (eg. where match id is 1)

-if there is one 'different' observation and others are 'market only' , then Survior='Y' for the case where it is 'different'

-if all are 'same' within a given match id, then survivor='Y' for the case with latest created date.  

Essentially, the output should be as follows:

Match ID                         Comments    Date            Survivor

1                                   Market Only

1                                   Different

1                                   Different

2                                  Market Only

2                                  Different                                   'Y'

3                                   Same          10-07-2015

3                                    Same          11-08-2015

3                                    Same          12-09-2015        'Y'

Any help is greatly appreciated. Thank you

[Patrick]

Below code works with your sample data.

data have;
  infile datalines dsd dlm=',' truncover;
  input Match_ID $ Comments :$15. Date :ddmmyy10.;
  format date date9.;
  datalines;
1,Market Only
1,Different
1,Different
2,Market Only
2,Different
3,Same,10-07-2015
3,Same,11-08-2015
3,Same,12-09-2015
;
run;

proc format;
  value $comments (default=1)
    'Market Only' = 'A'
    'Different'   = 'B'
    'Same'        = 'C'
  ;
run;

data inter;
  set have;
  _com=put(Comments,$comments.);
run;

proc transpose data=inter out=inter(drop=_name_) prefix=_comment;
  by Match_ID;
  var _com;
run;

data want(drop=_:);
  merge have inter;
  by Match_id;
  array com {*} _comment:;
  if last.match_id then
    do;
      call sortc(of com[*]);
      _string=cats(of com[*]);
      if prxmatch('/^A+B$/',strip(_string))>0 then Survivor='Y';
      if prxmatch('/^C+$/',strip(_string))>0 then Survivor='Y';
    end;
run;

True if the string starts with one or several 'A' and ends with exactly one 'B'

if prxmatch('/^A+B$/',strip(_string))>0 then Survivor='Y';

True if the string only contains one or several 'C'

if prxmatch('/^C+$/',strip(_string))>0 then Survivor='Y';

[ChrisNZ · Accepted solution]

data HAVE;
infile cards missover;
  input MATCH_ID   1.    COMMENTS $12.   DATE    mmddyy10.    ;
  format DATE date9.;
  cards;
1 Market Only
1 Different
1 Different
2 Market Only
2 Different
3 Same          10-07-2015
3 Same          11-08-2015
3 Same          12-09-2015
run;
proc sort ;
  by  MATCH_ID descending COMMENTS DATE;
data WANT; 
  set HAVE;
  by  MATCH_ID;
  if first.MATCH_ID then call missing(NB_DIFFERENT, NB_SAME, NB_ID);
  if COMMENTS='Different' then NB_DIFFERENT+1;
  if COMMENTS='Same'      then NB_SAME     +1;
  NB_ID+1;
  if last.MATCH_ID then do;
    if NB_DIFFERENT=1 then SURVIVOR='Y';
    if NB_SAME=NB_ID  then SURVIVOR='Y';
  end;
run;

[sasmaverick]

Thank you very much! Works mostly.