DATA Step, Macro, Functions and more

How to create a new variable using proc sql

Reply
Super Contributor
Posts: 272

How to create a new variable using proc sql

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

 

Super User
Posts: 3,106

Re: How to create a new variable using proc sql

[ Edited ]

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;
Respected Advisor
Posts: 4,649

Re: How to create a new variable using proc sql

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;
PG
Ask a Question
Discussion stats
  • 2 replies
  • 193 views
  • 3 likes
  • 3 in conversation