How to sort in descending order by SOC, PT

[HitmonTran]

Hi, 

I need to sort by Descending Counts for variable 'cALL' without ruining the sort order of aedecod nested in aebodsys.  The current sort order is alphabetical by aebodsys and aedecode nested inside. 

Current Code:

data pfinal1;
	set tcombine (drop=_name_)  ; 
  	by ord1 aebodsys AEDECOD desc;

	 if ord1 in (1 2) then do;
	     array _value  ctrtg1 ctrtg2 ctrtg3 ctrtg4 call ; 

	 	 do over _value;
	     	if _value = "" then _value="0 (0.0%)";
	     end;	
	  end;
	  
	total1 = sum(input(scan(call, 1), best.));

	if ord1 = 1 then ordx1 = 9999;

	if ord1 = 2 then do;
		   retain ordx1;
		   if AEDECOD = "" then do;
		       * ordx1 = total1;
			 subordx1 = 9999;
		   end;
		 * else subordx1 = total1;
	end;

	**reassign sort order for table output;
	if desc='Subjects with Any AEs, n (%)'   then do; aebodsys='Subjects with Any AEs, n (%)'; aedecod='Subjects with Any AEs, n (%)'; ord1=1; end;
	else if desc='Solicited Adverse Event'   then do; aebodsys='Subjects with Any AEs, n (%)'; aedecod='Subjects with Any AEs, n (%)'; desc='  Solicited'; ord1=2; end;
	else if desc='Unsolicited Adverse Event' then do; aebodsys='Subjects with Any AEs, n (%)'; aedecod='Subjects with Any AEs, n (%)';desc='  Unsolicited'; ord1=3; end;
	else if desc='Lab Abnormality' 			 then do; aebodsys='Subjects with Any AEs, n (%)'; aedecod='Subjects with Any AEs, n (%)';desc='  Lab Abnormalitites'; ord1=4; end;
	else if aebodsys='UNCODED' then ord1=6;
	else ord1=5;

	rename ctrtg1=TRTG1 ctrtg2=TRTG2 ctrtg3=TRTG3 ctrtg4=TRTG4;
run;

proc sort data= pfinal1 out= final1 ;
  by  descending ordx1  ord1 aebodsys /*descending subordx1*/ descending total1 aedecod desc ;
run;

in descending counts within SOC and PT . I attached the actual sas data. Current sort order of actual data:

[ballardw]

Please provide as either an attached SAS data set or better a data step.

I am partially concerned that this may require adding a sort value that requires lots of coding because what you show doesn't match your description. You are not sorting by SOC or PT at all, perhaps grouping by something but not sorting. Plus the values you show with all the aa bb cc seem extremely likely to be not related to your actual values and sorting is all about values.

[HitmonTran]

I attached an excel file of the actual data.

This is my current sort order

proc sort data= final1 ;
by descending ordx1 ord1 aebodsys descending total1 aedecod desc ;
run;

[Kurt_Bremser]

If the "actual data" is an Excel spreadsheet, you should ask your question in a MS Excel oriented forum.

If it is, OTOH, a SAS dataset, post it in a data step with datalines that creates the dataset.

You have been shown how to do this multiple times in your previous questions.

[HitmonTran]

the actual data is in sas7bdat. I exported to excel

[Kurt_Bremser]

---

@HitmonTran wrote:
the actual data is in sas7bdat. I exported to excel

---

Which renders it useless as a representation of SAS data, as all metadata (variable attributes) is lost. Post a data step with datalines.

[HitmonTran]

i've added the sas7bdat dataset, please take a look, thank you

[HitmonTran]

i've added the sas7bdat dataset, please take a look, thank you

[Kurt_Bremser]

So this is what the data looks like in a DATA step:

data have;
infile datalines dlm="|" dsd truncover;
input ord1 aebodsys :$36. AEDECOD :$47. desc :$char200. cAll :$20. TRTG1 :$20. TRTG2 :$20. total1 ordx1 subordx1;
datalines;
1|Subjects with Any AEs, n (%)|Subjects with Any AEs, n (%)|Subjects with Any AEs, n (%)|12 (60.0%)|6 (60.0%)|6 (60.0%)|12|9999|.
2|Subjects with Any AEs, n (%)|Subjects with Any AEs, n (%)|  Solicited|2 (10.0%)|0 (0.0%)|2 (20.0%)|2|9999|.
3|Subjects with Any AEs, n (%)|Subjects with Any AEs, n (%)|  Unsolicited|6 (30.0%)|3 (30.0%)|3 (30.0%)|6|9999|.
4|Subjects with Any AEs, n (%)|Subjects with Any AEs, n (%)|  Lab Abnormalitites|7 (35.0%)|5 (50.0%)|2 (20.0%)|7|9999|.
5|Blood and lymphatic system disorders||Blood and lymphatic system disorders|1 (5.0%)|1 (10.0%)|0 (0.0%)|1|9999|9999
5|Blood and lymphatic system disorders|Eosinophilia|  Eosinophilia|1 (5.0%)|1 (10.0%)|0 (0.0%)|1|9999|.
5|Ear and labyrinth disorders||Ear and labyrinth disorders|1 (5.0%)|0 (0.0%)|1 (10.0%)|1|9999|9999
5|Ear and labyrinth disorders|Inner ear disorder|  Inner ear disorder|1 (5.0%)|0 (0.0%)|1 (10.0%)|1|9999|.
5|Gastrointestinal disorders||Gastrointestinal disorders|4 (20.0%)|3 (30.0%)|1 (10.0%)|4|9999|9999
5|Gastrointestinal disorders|Nausea|  Nausea|2 (10.0%)|1 (10.0%)|1 (10.0%)|2|9999|.
5|Gastrointestinal disorders|Dental caries|  Dental caries|1 (5.0%)|1 (10.0%)|0 (0.0%)|1|9999|.
5|Gastrointestinal disorders|Diarrhoea|  Diarrhoea|1 (5.0%)|1 (10.0%)|0 (0.0%)|1|9999|.
5|Gastrointestinal disorders|Vomiting|  Vomiting|1 (5.0%)|1 (10.0%)|0 (0.0%)|1|9999|.
5|Infections and infestations||Infections and infestations|1 (5.0%)|1 (10.0%)|0 (0.0%)|1|9999|9999
5|Infections and infestations|Alveolar osteitis|  Alveolar osteitis|1 (5.0%)|1 (10.0%)|0 (0.0%)|1|9999|.
5|Infections and infestations|Tooth infection|  Tooth infection|1 (5.0%)|1 (10.0%)|0 (0.0%)|1|9999|.
5|Infections and infestations|Upper respiratory tract infection|  Upper respiratory tract infection|1 (5.0%)|1 (10.0%)|0 (0.0%)|1|9999|.
5|Investigations||Investigations|7 (35.0%)|4 (40.0%)|3 (30.0%)|7|9999|9999
5|Investigations|Blood creatine phosphokinase increased|  Blood creatine phosphokinase increased|3 (15.0%)|3 (30.0%)|0 (0.0%)|3|9999|.
5|Investigations|Activated partial thromboplastin time prolonged|  Activated partial thromboplastin time prolonged|1 (5.0%)|1 (10.0%)|0 (0.0%)|1|9999|.
5|Investigations|Alanine aminotransferase increased|  Alanine aminotransferase increased|1 (5.0%)|0 (0.0%)|1 (10.0%)|1|9999|.
5|Investigations|Aspartate aminotransferase increased|  Aspartate aminotransferase increased|1 (5.0%)|1 (10.0%)|0 (0.0%)|1|9999|.
5|Investigations|Blood calcium increased|  Blood calcium increased|1 (5.0%)|1 (10.0%)|0 (0.0%)|1|9999|.
5|Investigations|Blood creatine abnormal|  Blood creatine abnormal|1 (5.0%)|0 (0.0%)|1 (10.0%)|1|9999|.
5|Investigations|Blood creatinine increased|  Blood creatinine increased|1 (5.0%)|1 (10.0%)|0 (0.0%)|1|9999|.
5|Investigations|Electrocardiogram abnormal|  Electrocardiogram abnormal|1 (5.0%)|0 (0.0%)|1 (10.0%)|1|9999|.
5|Investigations|Haemoglobin decreased|  Haemoglobin decreased|1 (5.0%)|1 (10.0%)|0 (0.0%)|1|9999|.
5|Nervous system disorders||Nervous system disorders|1 (5.0%)|0 (0.0%)|1 (10.0%)|1|9999|9999
5|Nervous system disorders|Headache|  Headache|1 (5.0%)|0 (0.0%)|1 (10.0%)|1|9999|.
5|Psychiatric disorders||Psychiatric disorders|1 (5.0%)|1 (10.0%)|0 (0.0%)|1|9999|9999
5|Psychiatric disorders|Anxiety|  Anxiety|1 (5.0%)|1 (10.0%)|0 (0.0%)|1|9999|.
5|Skin and subcutaneous tissue disorde||Skin and subcutaneous tissue disorde|1 (5.0%)|0 (0.0%)|1 (10.0%)|1|9999|9999
5|Skin and subcutaneous tissue disorde|Rash|  Rash|1 (5.0%)|0 (0.0%)|1 (10.0%)|1|9999|.
5|Vascular disorders||Vascular disorders|1 (5.0%)|0 (0.0%)|1 (10.0%)|1|9999|9999
5|Vascular disorders|Diastolic hypertension|  Diastolic hypertension|1 (5.0%)|0 (0.0%)|1 (10.0%)|1|9999|.
6|UNCODED||UNCODED|1 (5.0%)|1 (10.0%)|0 (0.0%)|1|9999|9999
6|UNCODED|UNCODED|  UNCODED|1 (5.0%)|1 (10.0%)|0 (0.0%)|1|9999|.
;

From this, where would you want to have the sort order changed?

[DrAbhijeetSafai]

At least at the moment, I have same query. I will share here if the query gets solved or if I get answer. Thanks.

- Dr. Abhijeet Safai

[DrAbhijeetSafai]

I was able to solve it with the help of following paper. Especially their idea of creating numeric sorting variables for soc and pt worked. 

PharmaSUG China 2015 – Paper 02
Fifty Shades of Sorting
Haibin Shu, AccuClin Global Services LLC, Wayne, PA
John He, AccuClin Global Services LLC, Wayne, PA
Elena Rojco, DentaQuest, Salem, NH

Link: https://www.lexjansen.com/pharmasug-cn/2015/PT/PharmaSUG-China-2015-PT02.pdf

Thank you.

Dr. Abhijeet Safai