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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.