Collapsing Characters

[newsas007]

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

[Shmuel]

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]

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]

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;