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!
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.
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.
@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.
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: