topic Re: If all entries in a group are same or different in SAS Programming

If all entries in a group are same or different

Bhontu — Mon, 15 Jun 2020 05:19:07 GMT

Hi All,

Any help will be much appreciated.

Here is a replica of my dataset:

ID Type of Drug

1 T1

2 T2

3 T3

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.

Re: If all entries in a group are same or different

Bhontu — Mon, 15 Jun 2020 05:21:57 GMT

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.

Re: If all entries in a group are same or different

PeterClemmensen — Mon, 15 Jun 2020 05:35:40 GMT

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;

Re: If all entries in a group are same or different

Patrick — Mon, 15 Jun 2020 05:45:03 GMT

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;

Re: If all entries in a group are same or different

andreas_lds — Mon, 15 Jun 2020 06:23:08 GMT

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;

Re: If all entries in a group are same or different

yabwon — Mon, 15 Jun 2020 06:23:37 GMT

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

Re: If all entries in a group are same or different

Ksharp — Mon, 15 Jun 2020 10:54:31 GMT

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

Re: If all entries in a group are same or different

Bhontu — Mon, 15 Jun 2020 23:58:40 GMT

Hi All,

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