topic Re: creating flag for repeating values in SAS Programming

creating flag for repeating values

sonicview — Wed, 26 Aug 2020 19:01:18 GMT

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 1004 D1

02 1000 D1 Y

02 1001 D7

Thank you!

Re: creating flag for repeating values

PaigeMiller — Wed, 26 Aug 2020 19:15:07 GMT

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;

Re: creating flag for repeating values

sonicview — Wed, 26 Aug 2020 19:24:55 GMT

Thank you! It worked 🙂

Re: creating flag for repeating values

Reeza — Wed, 26 Aug 2020 19:29:07 GMT

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!

Re: creating flag for repeating values

novinosrin — Wed, 26 Aug 2020 19:48:31 GMT

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;