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.
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.
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;
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;
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;
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
proc sql; create table want as select * from have group by ID having count(distinct Type)>1; quit;
Hi All,
Thank you so much. All the options worked for me and more. Much appreciate.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.