BookmarkSubscribeRSS Feed
newsas007
Quartz | Level 8

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
3 REPLIES 3
Shmuel
Garnet | Level 18

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;
newsas007
Quartz | Level 8

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...

Shmuel
Garnet | Level 18

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;

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 981 views
  • 0 likes
  • 2 in conversation