Solved
Contributor
Posts: 69

# Compare Observations and Assign Value to a New Variable.

I have the following dataset:

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:

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

Accepted Solutions
Solution
‎10-12-2015 07:45 AM
PROC Star
Posts: 2,368

## Re: Compare Observations and Assign Value to a New Variable.

``````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 ;
data WANT;
set HAVE;
by  MATCH_ID;
if first.MATCH_ID then call missing(NB_DIFFERENT, NB_SAME, NB_ID);
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;``````

All Replies
Posts: 4,736

## Re: Compare Observations and Assign Value to a New Variable.

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;
'Market Only' = 'A'
'Different'   = 'B'
'Same'        = 'C'
;
run;

data inter;
set have;
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';``

Solution
‎10-12-2015 07:45 AM
PROC Star
Posts: 2,368

## Re: Compare Observations and Assign Value to a New Variable.

``````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 ;
data WANT;
set HAVE;
by  MATCH_ID;
if first.MATCH_ID then call missing(NB_DIFFERENT, NB_SAME, NB_ID);
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;``````
Contributor
Posts: 69

## Re: Compare Observations and Assign Value to a New Variable.

Thank you very much! Works mostly.
🔒 This topic is solved and locked.