topic Re: Creating new variables using result of an existing variable in SAS Programming

Creating new variables using result of an existing variable

sms1891 — Mon, 10 Feb 2020 18:48:01 GMT

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.

Re: Creating new variables using result of an existing variable

PeterClemmensen — Mon, 10 Feb 2020 20:23:45 GMT

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;

Re: Creating new variables using result of an existing variable

jklaverstijn — Mon, 10 Feb 2020 20:34:20 GMT

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.

Re: Creating new variables using result of an existing variable

PeterClemmensen — Mon, 10 Feb 2020 21:33:32 GMT

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

Re: Creating new variables using result of an existing variable

sms1891 — Mon, 10 Feb 2020 21:54:59 GMT

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!

Re: Creating new variables using result of an existing variable

Ksharp — Tue, 11 Feb 2020 04:19:11 GMT

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;