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!
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;
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;
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!
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;
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.