Creating new variables using result of an existing variable

[sms1891]

Dear all,

I want to identify tests that are done before 1st positive Result (i.e. all tests before 1st Result = 1) and all tests done after 1st positive Result (i.e. all tests done after 1st result = 1) as two separate variables. 

Here is what I need for the new variables:

ID    Count    Result     Test_Before   Test_After 

1        1             0            Yes

1        2             0            Yes

1        3             1                                  

1        4             0                                       Yes     

1        5             0                                       Yes        

1        6             1                                       Yes

2        1             0            

2        2             0            

3        1             1            

4        1             0            Yes

4        2             0            Yes

4        3             1

5        1             1            

5        2             0                                      Yes

5        3             1                                      Yes  

Thank you so much in advance for all the responses. I really appreciate it.

SM

[PeterClemmensen]

data have;
input ID Count Result;
datalines;
1 1 0 
1 2 0 
1 3 1 
1 4 0 
1 5 0 
1 6 1 
2 1 0 
2 2 0 
3 1 1 
4 1 0 
4 2 0 
4 3 1 
5 1 1 
5 2 0 
5 3 1 
;

data want(drop=_:);
   _f=0;
   do until (last.id);
      set have;
      by id;
      if result=1 & _f=0 then do;
         _count=count; _f=1;
      end;
   end;
   do until (last.id);
      set have;
      length Test_Before Test_After $3;
      Test_Before = '';
      Test_After = '';
      by id;
      if _count = . then do;
         Test_Before = '';
         Test_After = '';
      end;
      else if count < _count then Test_Before = 'Yes';
      else if count > _count then Test_After = 'Yes';
      output;
   end;

run;

[jklaverstijn · Accepted solution]

How about this?

data have;
	input id count result;
	cards;
1 1 0
1 2 0
1 3 1
1 4 0
1 5 0
1 6 1
2 1 0
2 2 0
3 1 1
4 1 0
4 2 0
4 3 1
5 1 1
5 2 0
5 3 1
;
proc sql;
	create table res1 as
		select id, min(count) as count1
		from t
			where result=1
				group by id;
quit;

data want;
	merge have res1;
	by id;
	if count<count1 then
		test_before = 'yes';
	else if count>count1 then
		test_after = 'yes';
run;

I create an intermediate set with al id's and the count at which the result was 1 for the first time. Then I left-join that with the original set to determine if a row was before or after the first result=1.

This all assumes the data was sorted by ID and Count. If not you need to add an additional sort step.

And yes, with some effort this can be rewritten in a single SQL. As usual, many roads lead to Rome.

Hope this helps,

- Jan.

[sms1891]

Data want;
	merge have res1;
	by id;
	if count<count1 then
		test_before = 'yes';
	else if count1 ne . and count>count1 then
		test_after = 'yes';

I modified the count1 statement and this is what I need!

Thank you! 

[PeterClemmensen]

Alternatively..

data want(drop=rc _:);
   if _N_=1 then do;
      declare hash h ();
      h.definekey ('id');
      h.definedata ('id','_count');
      h.definedone();
      
      do until (lr);
         set have(rename=count=_count) end=lr;
         if Result=1 then h.ref();
      end;
   end;

   set have;
   _count=.;

   if h.find()=0 then do;
      if      count < _count then Test_Before = 'Yes';
      else if count > _count then Test_After = 'Yes';
   end;
run;

Result:

ID Result Count Test_Before Test_After 
1  0      1     Yes   
1  0      2     Yes   
1  1      3         
1  0      4                 Yes 
1  0      5                 Yes 
1  1      6                 Yes 
2  0      1         
2  0      2         
3  1      1         
4  0      1     Yes   
4  0      2     Yes   
4  1      3         
5  1      1         
5  0      2                 Yes 
5  1      3                 Yes 

[Ksharp]

data have;
	input id count result;
	cards;
1 1 0
1 2 0
1 3 1
1 4 0
1 5 0
1 6 1
2 1 0
2 2 0
3 1 1
4 1 0
4 2 0
4 3 1
5 1 1
5 2 0
5 3 1
;
data want;
 do n=1 by 1 until(last.id);
  set have;
  by id;
  if result then has_result=1;
  if result and not found then do;found=1;_n=n;end;
 end;
 
 do n=1 by 1 until(last.id);
  set have;
  by id;
  Test_Before='   ';Test_After='   ';
  if has_result then do;
   if n<_n then Test_Before='Yes';
   if n>_n then Test_After='Yes';
  end;
  output;
 end;
drop n found _n;
run;