@drjorg : As I mentioned, one can only guess unless you provide some example records that closely approximate your actual data.
However, that said, there was a logical error in the way you modified the code, and I added the probably necessary 'i' modifier.
See if the following comes closer to what you want/expected:
data limit;
informat id 8.;
informat Class_I_p $1.;
informat Result_Date date9.;
format Result_Date date9.;
informat comments $255.;
input ID Class_I_p RESULT_DATE comments &;
cards;
1 1 02feb2019 On Monday I had 1.5 scoops of vanilla ICE cream, Tuesday we had 2 scoops of chocolate Ice cream, one day we also had 3 scoops of strawberry icE cream, but I can't remember when.
2 1 02feb2019 Over the week I had 4 scoops of raspberry ICE cream, 3 scoops of vanilla Ice cream
3 1 02feb2019 On 1/20/2019, I had 3 scoops of chocolate ice cream.
4 0 02feb2019 On 2/1/2019 I didn't want any ice cream
5 1 02feb2019 On 2/1/2019 I had 1 scoop of lemon iCe
;
data try6;
set limit(keep=ID Class_I_p RESULT_DATE comments);
i=0;
j=0;
ixp=0;
ixs=-2;
if Class_I_p=1 then do;
do while (findw(comments,"ICE",' ,','ei',ixs+3));
i+1;
if i eq 1 then ixs+3;
ixp=findw(comments,"ICE",' ,','ei',ixs)+ixp;
j+1;
SCOOPS=catx(' ',scan(comments,ixp-4,' '),scan(comments,ixp-3,' '));
Flavor = scan(comments,ixp-1,' ');
output;
ixs=findw(comments,"ice",' ,','i',ixs)+3;
end;
end;
run;
%transpose(data=try6,out=want, var=scoops flavor, id=j, by=id RESULT_DATE)
Art, CEO, AnalystFinder.com
... View more