BookmarkSubscribeRSS Feed
HitmonTran
Pyrite | Level 9

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:

 

10 REPLIES 10
ballardw
Super User

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
Pyrite | Level 9
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
Super User

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
Pyrite | Level 9
the actual data is in sas7bdat. I exported to excel
Kurt_Bremser
Super User

@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
Pyrite | Level 9
i've added the sas7bdat dataset, please take a look, thank you
HitmonTran
Pyrite | Level 9

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

Kurt_Bremser
Super User

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
Lapis Lazuli | Level 10
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
Dr. Abhijeet Safai
Certified Base and Clinical SAS Programmer
Associate Data Analyst
Actu-Real
DrAbhijeetSafai
Lapis Lazuli | Level 10

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

Dr. Abhijeet Safai
Certified Base and Clinical SAS Programmer
Associate Data Analyst
Actu-Real

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

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
  • 10 replies
  • 3155 views
  • 1 like
  • 4 in conversation