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
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.
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;
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.
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!
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.