Dear,
In my following data I need to create a flag variable based on a few conditions.
Please help in my code.
I need to flag OBS by group id and visit where pt between 0 and 1 and date1 less than date2 and max value of value variable .
For the data I need to flag OBS 2 with value=3 as it is the max value. please help thanks.
code;
proc sql;
create table two as
select *
from data
group by id,visit
having 0<pt<1 and date1 lt date2 and max(value);
quit;
data
id visit value pt date1 date2
1 1 2 0.5 2014-03-18T01:30 2014-03-19T01:30
1 1 3 0.65 2014-03-18T01:30 2014-03-19T01:30
1 1 1 0.75 2014-03-18T01:30 2014-03-19T01:30
1 2 1 0.5 2014-03-18T01:30 2014-03-19T01:30
output need;
id visit value pt date1 date2 flag
1 1 2 0.5 2014-03-18T01:30 2014-03-19T01:30
1 1 3 0.65 2014-03-18T01:30 2014-03-19T01:30 y
1 1 1 0.75 2014-03-18T01:30 2014-03-19T01:30
1 2 1 0.5 2014-03-18T01:30 2014-03-19T01:30
Adding a sub-query and a CASE expression will get you what you want:
proc sql;
create table two as
select A.*
,case
when A.value = B.value_max then 'y'
else ''
end as flag
from data as A
left join
(select id
,visit
,max(value) as value_max
from data
group by id,visit
) as B
on A.id = B.id
and A.visit = B.visit
;
quit;
Or simply:
proc sql;
create table two as
select *,
case when pt > 0 and pt < 1 and date1 < date2 and value = max(value) then "y" else " " end as flag
from data
group by id, visit;
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.