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