How to create a new variable using proc sql

[knveraraju91]

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

[SASKiwi]

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;

[PGStats]

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;