Looping across rows

[Xinxin]

Hello,

My data is as follows:

ID	DESC	TAKEN_TIME	Control	dose
1	LABE	28AUG13:18:16:00	No	40
1	LABE	28AUG13:18:56:00	No	80
1	LABE	28AUG13:19:14:00	No	80
1	HYDRA	28AUG13:19:37:00	XX	10
1	HYDRA	28AUG13:19:50:00	XX	10
1	HYDRA	28AUG13:21:20:00	XX	10

And I want output as follows:

ID	Initial Labe Date_Time	Initial Labe Dose	Extra_Pushes	Extra_Dose	Last Labe Date_Time	Last Failure Date_Time	Last Failure Drug	Total Failure Dosage	Control Date_Time
1	28AUG13:18:16:00	40	2	80+80	28AUG13:19:14:00	28AUG13:21:20:00	HYDRA	10+10+10	.

So, in short, add all doses for LABE except the initial dose and keep a count after the initial dose and add all doses if the drug is other than LABE. I need to do this for all IDs, only listed 1 here.

data test123;
input @1 ID 1. @2 DESC $5. @8 TAKEN_TIME : datetime. @25 Control $3. @28 dose;
format TAKEN_TIME datetime. ;

datalines;
1 LABE 28AUG13:18:16:00 No 40
1 LABE 28AUG13:18:56:00 No 80
1 LABE 28AUG13:19:14:00 No 80
1 HYDRA 28AUG13:19:37:00 XX 10
1 HYDRA 28AUG13:19:50:00 XX 10
1 HYDRA 28AUG13:21:20:00 XX 10
;
run;

Hope I made it clear....thank you in advance!

[ballardw]

I am afraid that your "failure" information is not well defined at all. Is that supposed to be any desc other than LABE?

Is the Total Failure Dosage only for the "Last Failure Drug" or for all "Failure drugs"?

What is a "control_time"?

Are your total doses actually wanting a string that reads "80+80" or the resolved value of 160?

Is the data ALWAYS all of the LABE followed by something else in the DESC variable or will LABE and other values be mixed by time within any given ID? If not and things are intermixed with LABE and other DESC then the "last failure" and such may need lots of additional explanation.

[Xinxin]

There are observations where the episode ends in control=Yes and I have figured out the programming for that. (not an ideal situation to split the data, but...I did it.)

The drug of interest is LABE. If a different drug, other than LABE  was used then the control=XX. Every episode ends with control=Yes, if controlled by LABE or XX if controlled by other drug, but still considered a failure as LABE didn't work. 

I do not want a string that reads "80+80" but just used it for explanation.

There are cases where control is no, no, yes, no, no, yes -- these are 2 episodes, I haven't figured programming for that either.

My following crude code gives what I need as the last row, but for pushes it should be 200-40, which I can do later...

data test124; set test123;
if desc = "LABE" then pushes+dose;
else if desc = "HYDR" then pushesxx+dose;
run;

LABE and other drugs will not be mixed within an ID. It will be only LABE and control =yes (maybe few no's in between) or LABE followed by another drug and then control will be xx.

[ballardw]

---

@Xinxin wrote:

There are cases where control is no, no, yes, no, no, yes -- these are 2 episodes, I haven't figured programming for that either.

---

Provide an example data step that illustrates some example data with this case.

The data is likely going to be amenable to use of BY processing with the NOTSORTED option.

The following code allows identifying first and last of groups of values. This may give a starting point for dealing with the more complex cases.

data example;
   input id desc $;
datalines;
1 LABE
1 LABE
1 XXX
1 XXX
1 LABE
1 LABE
1 XXX
1 XXX
2 LABE
2 XXX
2 XXX
;
RUN;

data byexample;
   set example;
   by notsorted id desc;
   startid=first.id;
   endid  =last.id;
   startdesc= first.desc;
   enddesc  = last.desc;
run;

The 1 values for started, startdesc and enddesc indicate "true" for being first or last, 0 are false.

Note that single value group (only one id desc before the desc changes) has the first and last true for desc as shown by the first record for Id 2 above.