Hi all,
Here is my dataset with all characters. I want to extract the drug name from the ReactionToDrug column and collapse symptoms in one row. Along with the ADR classification that is listed in the ItemName column.
For example, ID#1 has Docetaxel, and had two symptoms: Cardiac and Pulmonary. Also, there was no ADR classification in the ItemName so this row is empty.
For ID#2, it has Cetuximab drug and 4 different symptoms: Cardiac, Pulmonary, Dermatologic, Hypersensitvity. It also has an ADR classification as “Severe: Is life threatening or causes permanent injury or death”. There are some duplicates, but it needs to be counted only once based on the drug.
For ID#3, The drug is Cetuximab and has 2 symptoms: Hives and Itching. Also, has the ADR classification as “Minor: requires no medical treatment or has no effect on continuation of therapy.”
For ID#4, it has 3 different drugs with corresponding symptoms and ADR classification.
Dataset Have:
SubjectID | ReactionToDrug | ItemName | ValueALL |
1 | DOCEtaxel ivpb, ABFFETGEYGHDFDFDRWREYTRHGDFD | Cardiac | Tachycardia |
1 | DOCEtaxel ivpb, ABFFETGEYGHDFDFDRWREYTRHGDFD | Pulmonary | Shortness of Breath |
2 | cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF | ADR Classification | Severe: Is life threatening |
2 | cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF | Cardiac | Chest Pain |
2 | cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF | Cardiac | Hypotension |
2 | cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF | Cardiac | Tachycardia |
2 | cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF | Dermatologic | Flushing |
2 | cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF | Hypersensitivity | Anaphylaxis |
2 | cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF | Hypersensitivity | Bronchospasm/stridor |
2 | cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF | Pulmonary | Bronchospasm |
2 | cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF | Pulmonary | Shortness of Breath |
2 | cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF | ADR Classification | Severe: Is life threatening |
2 | cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF | Cardiac | Chest Pain |
2 | cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF | Cardiac | Hypotension |
2 | cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF | Cardiac | Tachycardia |
2 | cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF | Dermatologic | Flushing |
2 | cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF | Hypersensitivity | Anaphylaxis |
2 | cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF | Hypersensitivity | Bronchospasm/stridor |
2 | cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF | Pulmonary | Bronchospasm |
2 | cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF | Pulmonary | Shortness of Breath |
3 | cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF | ADR Classification | Minor: requires no medical treatment |
3 | cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF | Hypersensitivity | Hives |
3 | cetuximab ivpb, SDGGFHFFGHFGFDGDFGDGDGDGDGDHFHFHFHFF | Hypersensitivity | Itching |
4 | nivolumab IVPB, GDFDFDFDGDHFHFHFGDGDGDGDGDG | ADR Classification | Minor: requires no medical treatment |
4 | nivolumab IVPB, GDFDFDFDGDHFHFHFGDGDGDGDGDG | Cardiac | Tachycardia |
4 | nivolumab IVPB, GDFDFDFDGDHFHFHFGDGDGDGDGDG | Dermatologic | Flushing |
4 | PACLitaxel ivpb, FDGDGDHFHFHFJGJGJGJGJGJGJGJ | ADR Classification | Significant: Results in hospital admission, |
4 | PACLitaxel ivpb, FDGDGDHFHFHFJGJGJGJGJGJGJGJ | Dermatologic | Flushing |
4 | PACLitaxel ivpb, FDGDGDHFHFHFJGJGJGJGJGJGJGJ | Pulmonary | Shortness of Breath |
4 | DOCEtaxel ivpb, ABFFETGEYGHDFDFDRWREYTRHGDFD | ADR Classification | Significant: Results in hospital admission, |
4 | DOCEtaxel ivpb, ABFFETGEYGHDFDFDRWREYTRHGDFD | Dermatologic | Flushing |
Data Want:
ID | DrugName | Symptoms | ADR Classification |
1 | DOCEtaxel | Cardiac, Pulmonary | |
2 | cetuximab | Cardiac, Pulmonary, Dermatologic, Hypersensitvity | Severe: Is life threatening |
3 | cetuximab | Hives, Itching | Minor: requires no medical treatment |
4 | Nivolumab | Cardiac, Dermatologic | Minor: requires no medical treatment |
4 | Paclitaxel | Dermatologic, Pulmonary | Significant: Results in hospital admission |
4 | Docetaxel | Dermatologic | Significant: Results in hospital admission |
Adapt next code to needs:
data temp1;
set have;
DrugName = scan(ReactionToDrug,1);
drop ReactionToDrug;
run;
proc sort data=temp1 out=temp2 nodupkey;
by SubjectID DrugName ItemName ValuAll;
run;
data want;
set temp2;
by SubjectID DrugName;
length Symptoms $100;
retain Symptoms;
if first.DrugName then Symptoms = ItemName;
else do;
Symptoms = (catx(',' , ItemName));
ADR_Classification = ......; /* rule is not clear */
end;
if last.DrugName then output;
run;
Thank you for this....
The first part of the program works. But I can't seem to figure out Symptoms and the ADR_Classification variables. Symptoms is all the categories under itemname but except ADR_classification.
So the ADR_classification variable is under/one of the categories in the ItemName variable. The corresponding value in the ValueAll variable is what goes under the ADR_classification. For example: 'Severe: Is life threatening'
Thanks...
I hope next code is what you want:
data temp1;
set have;
DrugName = scan(ReactionToDrug,1);
drop ReactionToDrug;
run;
proc sort data=temp1 out=temp2 nodupkey;
by SubjectID DrugName ItemName ValueAll;
run;
data want;
set temp2;
by SubjectID DrugName;
length Symptoms $100;
retain Symptoms;
if first.DrugName then Symptoms = ItemName;
else do;
Symptoms = (catx(',' , ItemName));
If Item_name = 'ADR_Classification' then
ADR_Classification = ValueAll;
end;
if last.DrugName then output;
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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: