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

[Alexxxxxxx]

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?

Thx in advance.

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;

[novinosrin · Accepted solution]

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;