BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasmaverick
Obsidian | Level 7

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20
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;

View solution in original post

3 REPLIES 3
Patrick
Opal | Level 21

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
Tourmaline | Level 20
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
Obsidian | Level 7
Thank you very much! Works mostly.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 824 views
  • 0 likes
  • 3 in conversation