Pyrite | Level 9

## whether the character value in different rows in a group of observations is different

Hi,

I am trying to know whether the character value in different rows in a group of observations is different?

By using Table 1 as an example

 obs ID date level 1 1 02/20/2020 B 2 1 02/20/2020 B 3 1 02/20/2020 A 4 1 04/08/2020 A 5 1 04/08/2020 CCC 6 2 02/20/2020 C 7 2 02/20/2020 C

The obs 1 to obs 3 belong to the same group because ID and date for them are the same (i.e., ID=1, date=02/20/2020).

Similarly, obs 4 and obs 5 belong to the same group, and obs 6 and obs7 belong to the same group.

For these three groups if the value in the level variable is not the same, then I expect to have flag=1, otherwise flag=0.

e.g., for the first group (obs1-obs3), the level of them are different, i.e., level='B' in obs 1 and obs 2, while level='A' in obs 3, then flag of them is 1.

I expect to have Table2 like below

 obs ID date level flag 1 1 02/20/2020 B 1 2 1 02/20/2020 B 1 3 1 02/20/2020 A 1 4 1 04/08/2020 A 1 5 1 04/08/2020 CCC 1 6 2 02/20/2020 C 0 7 2 02/20/2020 C 0

Could you please give me some suggestions?

```data table1;
infile cards dsd  dlm=",";
input
ID \$
date :mmddyy10.
level \$
;
format
date mmddyy10.
;
cards;
1,02/20/2020,B
1,02/20/2020,B
1,02/20/2020,A
1,04/08/2020,A,
1,04/08/2020,CCC
2,02/20/2020,C
2,02/20/2020,C
;;;;
run;```

1 ACCEPTED SOLUTION

Accepted Solutions
Tourmaline | Level 20

## Re: whether the character value in different rows in a group of observations is different

``````data table1;
infile cards dsd  dlm=",";
input
ID \$
date :mmddyy10.
level \$
;
format
date mmddyy10.
;
cards;
1,02/20/2020,B
1,02/20/2020,B
1,02/20/2020,A
1,04/08/2020,A,
1,04/08/2020,CCC
2,02/20/2020,C
2,02/20/2020,C
;;;;

proc sql;
create table want as
select *, count(distinct level)>1 as flag
from table1
group by id
order by id,date;
quit;``````
Tourmaline | Level 20

## Re: whether the character value in different rows in a group of observations is different

``````data table1;
infile cards dsd  dlm=",";
input
ID \$
date :mmddyy10.
level \$
;
format
date mmddyy10.
;
cards;
1,02/20/2020,B
1,02/20/2020,B
1,02/20/2020,A
1,04/08/2020,A,
1,04/08/2020,CCC
2,02/20/2020,C
2,02/20/2020,C
;;;;

proc sql;
create table want as
select *, count(distinct level)>1 as flag
from table1
group by id
order by id,date;
quit;``````
Discussion stats