Hi,
Can someone help me please?
I have the following data set and would like to count the number of iterations on 'outcome' before values THIRDPARTY and RIGHTPARTY are true, i.e. on patid 1 (when sorted by date) the value of THIRDPARTY is met on the very first record so the count will be 1. I'd like to retain this value even though the condition is true on record #5 for the same ID. On padtid 2 the count will be 4 .. the idea is to retain the very first instance of either THIRDPARTY or RIGHTPARTY.
Thanks in advance!
data dsn;
input patid calldate date9. outcome $15. ;
datalines;
1 01JAN2012 THIRDPARTY
1 02JAN2012 NO
1 03JAN2012 NO
1 04JAN2012 THIRDPARTY
2 01JAN2012 NO
2 02JAN2012 NO
2 03JAN2012 NO
3 01JAN2012 NO
3 02JAN2012 THIRDPARTY
4 02JAN2012 THIRDPARTY
4 03JAN2012 NO
2 04JAN2012 RIGHTPARTY
2 05JAN2012 RIGHTPARTY
1 02JAN2012 NO
5 01JAN2012 NO
5 02JAN2012 NO
;
run;
data dsn2;
set dsn;
format calldate date9.;
run;
proc sort data = dsn2;
by patid calldate;
run;
hi ... one way ...
data dsn;
input patid calldate :date9. outcome :$15. ;
format calldate date9.;
datalines;
1 01JAN2012 THIRDPARTY
1 02JAN2012 NO
1 03JAN2012 NO
1 04JAN2012 THIRDPARTY
2 01JAN2012 NO
2 02JAN2012 NO
2 03JAN2012 NO
3 01JAN2012 NO
3 02JAN2012 THIRDPARTY
4 02JAN2012 THIRDPARTY
4 03JAN2012 NO
2 04JAN2012 RIGHTPARTY
2 05JAN2012 RIGHTPARTY
1 02JAN2012 NO
5 01JAN2012 NO
5 02JAN2012 NO
;
proc sort data=dsn;
by patid calldate;
run;
data want (keep=patid calldate when);
do j=1 by 1 until (last.patid);
set dsn;
by patid;
if missing(when) and find(outcome,'PARTY') then when = j;
end;
if missing(when) then call missing(calldate);
run;
Here is one way using the programming pattern known as a double DOW loop instead of the RETAIN function.
The variable COUNT will have the value you wanted. The variable OBS will end up with the number of observations for that PATID. If you do not need it then you can drop it.
data dsn;
input patid calldate date9. outcome $15. ;
format calldate date9.;
datalines;
1 01JAN2012 THIRDPARTY
1 02JAN2012 NO
1 03JAN2012 NO
1 04JAN2012 THIRDPARTY
2 01JAN2012 NO
2 02JAN2012 NO
2 03JAN2012 NO
3 01JAN2012 NO
3 02JAN2012 THIRDPARTY
4 02JAN2012 THIRDPARTY
4 03JAN2012 NO
2 04JAN2012 RIGHTPARTY
2 05JAN2012 RIGHTPARTY
1 02JAN2012 NO
5 01JAN2012 NO
5 02JAN2012 NO
run;
proc sort;
by patid calldate;
run;
data want ;
do obs=1 by 1 until(last.patid);
set dsn ;
by patid calldate;
if count=. and outcome ne 'NO' then count=obs;
end;
do until(last.patid);
set dsn;
by patid calldate;
output;
end;
run;
Thanks Tom!!
This works and outputs the records I need!
Appreciate the swift response!!
hi ... one way ...
data dsn;
input patid calldate :date9. outcome :$15. ;
format calldate date9.;
datalines;
1 01JAN2012 THIRDPARTY
1 02JAN2012 NO
1 03JAN2012 NO
1 04JAN2012 THIRDPARTY
2 01JAN2012 NO
2 02JAN2012 NO
2 03JAN2012 NO
3 01JAN2012 NO
3 02JAN2012 THIRDPARTY
4 02JAN2012 THIRDPARTY
4 03JAN2012 NO
2 04JAN2012 RIGHTPARTY
2 05JAN2012 RIGHTPARTY
1 02JAN2012 NO
5 01JAN2012 NO
5 02JAN2012 NO
;
proc sort data=dsn;
by patid calldate;
run;
data want (keep=patid calldate when);
do j=1 by 1 until (last.patid);
set dsn;
by patid;
if missing(when) and find(outcome,'PARTY') then when = j;
end;
if missing(when) then call missing(calldate);
run;
Thanks MikeZdeb! Perfect, exactly what I needed.
Thank you for responsding so quickly! Very helpful!
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.