If all entries in a group are same or different

[Bhontu]

Hi All,

Any help will be much appreciated.

Here is a replica of my dataset:

ID            Type of Drug     

1              T1

1              T1

2              T2

2              T2

2              T2

2              T2

3              T3

3              T3

4              T4

4              T4

4              TX

I want to find out if for each ID the entries under variable "type of drug" are the same or not.  If they are not the same then how can I identify the IDs for which entry for variable "type of drug" is not same throughout? In the example dataset above, each entry under "type of drug" for ID 4 is different. So I want the code to identify ID 4. 

Thank you in advance.

[Bhontu]

Please assume all variables are numeric. And, I want to identify all IDs that have different values for the variable "type of drug". Preferable in SQL. If not, any other way also will work.

Thank you.

[PeterClemmensen]

How about

data have;
input ID Type $;
datalines;
1 T1
1 T1
2 T2
2 T2
2 T2
2 T2
3 T3
3 T3
4 T4
4 T4
4 TX
;

proc sql;
    create table want as
    select *, count(distinct Type)>1 as flag
    from have 
    group by ID;
quit;

[Patrick]

Below if you also want to know how many distinct types you've got for an ID with more than one type.

data have;
  input ID Type $;
  datalines;
1 T1
1 T1
2 T2
2 T2
2 T2
2 T2
3 T3
3 T3
4 T4
4 T4
4 TX
;

proc sql;
/*  create table want as*/
    select ID, count(distinct Type) as n_types
    from have 
    group by ID
    having n_types>1;
quit;

[andreas_lds]

You can, of course, use a data step to create a dataset containing all observations not having the the same type as the first observation (per ID).

data want;
	set have;
	by ID;
	
	lastType = lag(Type);
	
	if not first.ID and lastType ^= Type then output;
run;

[yabwon]

Hi,

you could also use double DoW-loop to recognise group with doubles and output it all at the same time:

data have;
input ID Type $;
datalines;
1 T1
1 T1
2 T2
2 T2
2 T2
2 T2
3 T3
3 T3
4 T4
4 T4
4 TX
;
run;

data want;
  do _N_ = 1 by 1  until(last.ID);
    set have;
    by id;
    if first.id then _first_ = Type;
    _marker_ = (_first_ ^= Type);
  end;

  do _N_ = 1 to _N_;
    set have;
    if _marker_ then output;
  end;
  drop _:;
run;
proc print data  = want;
run;

Here is classic article by @hashman which describe the idea of DoW-loop: https://support.sas.com/resources/papers/proceedings13/126-2013.pdf

All the best

Bart

[Ksharp]

proc sql;
 create table want as
    select *
    from have 
    group by ID
    having count(distinct Type)>1;
quit;

[Bhontu]

Hi All,

Thank you so much. All the options worked for me and more. Much appreciate.