topic Re: How to create a new variable using proc sql in SAS Programming

How to create a new variable using proc sql

knveraraju91 — Sun, 05 Feb 2017 21:02:25 GMT

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

Re: How to create a new variable using proc sql

SASKiwi — Mon, 06 Feb 2017 02:23:33 GMT

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;

Re: How to create a new variable using proc sql

PGStats — Mon, 06 Feb 2017 04:12:16 GMT

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;