creating flag for repeating values

[sonicview]

Hi! 

How I can create a flag for repeating value. This is the case. If a subject reappears in the same study for the same drug then no flag. But if the same subject appears in another study for the same drug then flag = "Y" for both studies.

Here is sample data with flag values which I want to derive.

study   subject    drug   flag

01       1000          D1      Y

01       1001          D2 

01       1001          D2

01       1004          D1

02       1000          D1       Y

02       1001          D7

Thank you!

[PaigeMiller · Accepted solution]

This code creates a flag that is 1 if the drug comes from two (or more) studies, and 0 otherwise.

proc sql;
	create table want as select *,count(distinct study)>1 as flag from have
	group by subject,drug order by study,subject,drug;
quit;

[sonicview]

Thank you! It worked 🙂

[Reeza]

Here's one way - and it also adds a count to indicate how many studies the person was in with that drug. 

data have;
infile cards truncover;
input study   subject    drug  $ flag $;
cards;
01       1000          D1      Y
01       1001          D2 
01       1001          D2
01       1004          D1
02       1000          D1       Y
02       1001          D7
;;;;

proc sql;
create table want as
select *, count(distinct study) as num_studies, 
	case when calculated num_studies > 1 then 'Y' 
	else " " 
	end as calculated_flag
from have
group by subject, drug
order by study, subject;
quit;

---

@sonicview wrote:

Hi! 

How I can create a flag for repeating value. This is the case. If a subject reappears in the same study for the same drug then no flag. But if the same subject appears in another study for the same drug then flag = "Y" for both studies.

Here is sample data with flag values which I want to derive.

study   subject    drug   flag

01       1000          D1      Y

01       1001          D2 

01       1001          D2

01       1004          D1

02       1000          D1       Y

02       1001          D7

Thank you!

---

[novinosrin]

Sir @PaigeMiller  seems to be in supreme form lately. Kudos!

@sonicview  If you are really after Y and ' ', you could use IFC as an extension in Mr Miller's logic-

proc sql;
 create table want as 
 select *,ifc(count(distinct study)>1,'Y',' ') as flag 
 from have
 group by subject,drug 
 order by study,subject,drug;
quit;