Data sorting as per two variable which has missing values?

[k26_23]

I need to sort the table A below in which the values needs to be in the output as per the variable 'Sort-order' and when variable 'ID' is not missing i need all the same ID values data to grouped (like all the TL01 values one by one and then followed by all TL02 and all TL03 values etc...)

example the input data set looks like this :

Table A

Subject	Site	ID	Description	Parameter	Parameter description	Sort-order
101-276	101			NTLDAT	Date of Assessment	01
101-276	101	NTL01	CARCINOMATOSIS:CARCINOMATOSIS	NTLSTAT	Status	02
101-276	101	NTL02	LIVER:LIVER LESIONS	NTLSTAT	Statu	02
101-276	101			TLDAT	Date of Assessment	03
101-276	101	TL01	ABDOMEN:RUQ OMENTAL MET	TLMEAS_C	Measurement	04
101-276	101	TL02	ABDOMEN:LEFT ABDOMEN OMENTAL MET	TLMEAS_C	Measurement	04
101-276	101	TL03	LIVER:LEFT LIVER DOME	TLMEAS_C	Measurement	04
101-276	101	TL04	LIVER:RIGHT LIVER DONE	TLMEAS_C	Measurement	04
101-276	101	TL01	ABDOMEN:RUQ OMENTAL MET	TLYN1	Was lesion assessed at this visit	05
101-276	101	TL02	ABDOMEN:LEFT ABDOMEN OMENTAL MET	TLYN1	Was lesion assessed at this visit	05
101-276	101	TL03	LIVER:LEFT LIVER DOME	TLYN1	Was lesion assessed at this visit	05
101-276	101	TL04	LIVER:RIGHT LIVER DONE	TLYN1	Was lesion assessed at this visit	05
101-276	101	TL01	ABDOMEN:RUQ OMENTAL MET	TLYN2	Check if lesion is too small to measure	06
101-276	101	TL02	ABDOMEN:LEFT ABDOMEN OMENTAL MET	TLYN2	Check if lesion is too small to measure	06
101-276	101	TL03	LIVER:LEFT LIVER DOME	TLYN2	Check if lesion is too small to measure	06
101-276	101	TL04	LIVER:RIGHT LIVER DONE	TLYN2	Check if lesion is too small to measure	06
101-276	101			TLSUM_C	Sum of Diameters	07
101-276	101			NLDAT	Date of Assessment	08
101-276	101	NL01	LIVER:RIGHT LOBE LIVER LESION	NLSTAT	Status	09
101-276	101	NL02	LIVER:LIVER LESIONS	NLSTAT	Status	09
101-276	101	NL01	LIVER:RIGHT LOBE LIVER LESION	NLMEAS	Measurement	10
101-276	101	NL02	LIVER:LIVER LESIONS	NLMEAS	Measurement	10
101-276	101			RSDAT	Date of Assessment	11
101-276	101			RSORRES1	RECIST 1.1 Target Response	12
101-276	101			RSORRES2	RECIST 1.1 Non-Target Response	13
101-276	101			RSYN1	Were unequivocal new lesions identified	14
101-276	101			RSORRES4	Tumor Biomarker Response	15
101-276	101			TLPCNTCH	% Change from Baseline	16
101-276	101			TLNADIR	% Change from NADIR	17
101-276	101			RSORRES3	RECIST 1.1 Overall Response	18

and i want the output to be sorted as below. 

Subject	Site	ID	Description	Parameter	Parameter description	Sort-order
101-276	101			NTLDAT	Date of Assessment	01
101-276	101	NTL01	CARCINOMATOSIS:CARCINOMATOSIS	NTLSTAT	Status	02
101-276	101	NTL02	LIVER:LIVER LESIONS	NTLSTAT	Status	02
101-276	101			TLDAT	Date of Assessment	03
101-276	101	TL01	ABDOMEN:RUQ OMENTAL MET	TLMEAS_C	Measurement	04
101-276	101	TL01	ABDOMEN:RUQ OMENTAL MET	TLYN1	Was lesion assessed at this visit	05
101-276	101	TL01	ABDOMEN:RUQ OMENTAL MET	TLYN2	Check if lesion is too small to measure	06
101-276	101	TL02	ABDOMEN:LEFT ABDOMEN OMENTAL MET	TLMEAS_C	Measurement	04
101-276	101	TL02	ABDOMEN:LEFT ABDOMEN OMENTAL MET	TLYN1	Was lesion assessed at this visit	05
101-276	101	TL02	ABDOMEN:LEFT ABDOMEN OMENTAL MET	TLYN2	Check if lesion is too small to measure	06
101-276	101	TL03	LIVER:LEFT LIVER DOME	TLMEAS_C	Measurement	04
101-276	101	TL03	LIVER:LEFT LIVER DOME	TLYN1	Was lesion assessed at this visit	05
101-276	101	TL03	LIVER:LEFT LIVER DOME	TLYN2	Check if lesion is too small to measure	06
101-276	101	TL04	LIVER:RIGHT LIVER DONE	TLMEAS_C	Measurement	04
101-276	101	TL04	LIVER:RIGHT LIVER DONE	TLYN1	Was lesion assessed at this visit	05
101-276	101	TL04	LIVER:RIGHT LIVER DONE	TLYN2	Check if lesion is too small to measure	06
101-276	101			TLSUM_C	Sum of Diameters	07
101-276	101			NLDAT	Date of Assessment	08
101-276	101	NL01	LIVER:RIGHT LOBE LIVER LESION	NLMEAS	Measurement	09
101-276	101	NL01	LIVER:RIGHT LOBE LIVER LESION	NLSTAT	Status	10
101-276	101	NL02	LIVER:LIVER LESIONS	NLMEAS	Measurement	09
101-276	101	NL02	LIVER:LIVER LESIONS	NLSTAT	Status	10
101-276	101			RSDAT	Date of Assessment	11
101-276	101			RSORRES1	RECIST 1.1 Target Response	12
101-276	101			RSORRES2	RECIST 1.1 Non-Target Response	13
101-276	101			RSYN1	Were unequivocal new lesions identified	14
101-276	101			RSORRES4	Tumor Biomarker Response	15
101-276	101			TLPCNTCH	% Change from Baseline	16
101-276	101			TLNADIR	% Change from NADIR	17
101-276	101			RSORRES3	RECIST 1.1 Overall Response	18

[Tom]

I do not understand what you are saying. Can you make a better example that more clearly shows what it is that you need to do?

The current dataset does not really help explain the issue very well.  Subject and Site are constants.  SORT_ORDER seems to be the same as PARAMETER/PARAMETER_DESCRIPTION.

And there are not that many ties for the value of SORT_ORDER .   Only 04, 05 and 06 have more than 2 observations.  And those all have unique values of ID.

[Ksharp · Accepted solution]

Assuming I understood what you mean.

options parmcards=x;
filename x temp;
parmcards;
101-276  101    .    .    NTLDAT    Date of Assessment 01
101-276  101   NTL01   CARCINOMATOSIS:CARCINOMATOSIS    NTLSTAT   Status  02
101-276  101   NTL02   LIVER:LIVER LESIONS   NTLSTAT  Statu   02
101-276  101    .    .   TLDAT   Date of Assessment  03
101-276  101   TL01    ABDOMEN:RUQ OMENTAL MET   TLMEAS_C    Measurement 04
101-276  101   TL02    ABDOMEN:LEFT ABDOMEN OMENTAL MET    TLMEAS_C    Measurement 04
101-276  101   TL03    LIVER:LEFT LIVER DOME    TLMEAS_C    Measurement 04
101-276  101   TL04    LIVER:RIGHT LIVER DONE   TLMEAS_C    Measurement 04
101-276  101   TL01    ABDOMEN:RUQ OMENTAL MET  TLYN1   Was lesion assessed at this visit   05
101-276  101   TL02    ABDOMEN:LEFT ABDOMEN OMENTAL MET    TLYN1   Was lesion assessed at this visit   05
101-276  101   TL03    LIVER:LEFT LIVER DOME   TLYN1   Was lesion assessed at this visit   05
101-276  101   TL04    LIVER:RIGHT LIVER DONE   TLYN1   Was lesion assessed at this visit   05
101-276  101   TL01    ABDOMEN:RUQ OMENTAL MET  TLYN2   Check if lesion is too small to measure 06
101-276  101   TL02    ABDOMEN:LEFT ABDOMEN OMENTAL MET     TLYN2   Check if lesion is too small to measure 06
101-276  101   TL03    LIVER:LEFT LIVER DOME    TLYN2   Check if lesion is too small to measure 06
101-276  101   TL04    LIVER:RIGHT LIVER DONE   TLYN2   Check if lesion is too small to measure 06
101-276  101    .   .  TLSUM_C   Sum of Diameters    07
101-276  101    .   .  NLDAT   Date of Assessment  08
101-276  101   NL01    LIVER:RIGHT LOBE LIVER LESION    NLSTAT  Status  09
101-276  101   NL02    LIVER:LIVER LESIONS   NLSTAT  Status  09
101-276  101   NL01    LIVER:RIGHT LOBE LIVER LESION   NLMEAS   Measurement 10
101-276  101   NL02    LIVER:LIVER LESIONS   NLMEAS   Measurement 10
101-276  101    .   .  RSDAT   Date of Assessment  11
101-276  101    .   .  RSORRES1    RECIST 1.1   Target Response  12
101-276  101    .   .  RSORRES2    RECIST 1.1   Non-Target Response  13
101-276  101    .   .  RSYN1   Were unequivocal new lesions identified 14
101-276  101    .   .  RSORRES4    Tumor Biomarker Response    15
101-276  101    .   .  TLPCNTCH    % Change from Baseline  16
101-276  101    .   .  TLNADIR   % Change from NADIR 17
101-276  101    .   .  RSORRES3    RECIST 1.1 Overall R
;


data x;
infile x expandtabs truncover dlm=' ';
input (Subject	Site	ID	Description 	Parameter	Parameterdescription) (& $80.);
missing=missing(id);
run;
data x1;
set x;
by Site Subject missing notsorted;
group+first.missing;
start=compress(id,,'d');
drop missing;
run;
proc sort data=x1 out=x2;
by group start id;
run;




data want;
if _n_=1 then do;
 if 0 then set x2;
 length sort_order 8;
 declare hash h();
 h.definekey('site','subject','parameter');
 h.definedata('sort_order');
 h.definedone();
end;
set x2;
by  Site Subject ;
if first.Subject then n=0;
if h.find() ne 0 then do;
 n+1;sort_order=n;h.add();
end;
drop n group start;
run;

[FreelanceReinh]

Hello @k26_23,

Try this:

/* Create temporary variable describing block structure */

data _tmp / view=_tmp;
set have;
by subject site;
_block=ifn(first.site,1,sum(_block, (id=' ') ne (lag(id)=' ')));
retain _block;
run;

/* Use the temporary variable to obtain the desired sort order */

proc sql;
create table want(drop=_block) as
select * from _tmp
order by subject, site, _block, id, sort_order;
drop view _tmp;
quit;

[dxiao2017]

Hi k26_23,
What dataset is this ? What is NT, NL, and TL? Are they domain name or specific column (eg., like link id) values?

[dxiao2017]

Hi @k26_23 , this is your data set A. I have not figure out the solution for your request, also I do not quiet understand the intention of your request, i.e., what kind of dataset you would like to create, and what's for?

It looks to me like you are working on a dataset that has link ID to other datasets. But what's the sort-order column for? Looking from your dataset A, this column is character (if it is numeric the '01' to '06' would be displayed like '1' to '6', without the leading 0), so what's this column for (I converted this column to numeric, incase it is needed as an order variable)?

And it looks to me like, by the sort-order column you created a number for each Parameter column value, if so why not just order your data according to the Parameter column? Are you prompting some convoluted question and a false request that confused yourself? I tried some sql step but did not get the dataset you request. I'll post it here also.

data tumor1;
   input Subject $1-7 Site 9-11 ID $13-17	Description $32.	
         Parameter $51-61 ParamDesc $43. sortorder $12.;
   sortordernum=input(sortorder,8.);
   datalines;
101-276	101	 	 	                                    NTLDAT	      Date of Assessment	                        01
101-276	101	NTL01	CARCINOMATOSIS:CARCINOMATOSIS	    NTLSTAT	      Status	                                    02
101-276	101	NTL02	LIVER:LIVER LESIONS	                NTLSTAT	      Statu	                                        02
101-276	101	 	 	                                    TLDAT	      Date of Assessment	                        03
101-276	101	TL01	ABDOMEN:RUQ OMENTAL MET	            TLMEAS_C	  Measurement	                                04
101-276	101	TL02	ABDOMEN:LEFT ABDOMEN OMENTAL MET	TLMEAS_C	  Measurement	                                04
101-276	101	TL03	LIVER:LEFT LIVER DOME	            TLMEAS_C	  Measurement	                                04
101-276	101	TL04	LIVER:RIGHT LIVER DONE	            TLMEAS_C	  Measurement	                                04
101-276	101	TL01	ABDOMEN:RUQ OMENTAL MET	            TLYN1	      Was lesion assessed at this visit	            05
101-276	101	TL02	ABDOMEN:LEFT ABDOMEN OMENTAL MET	TLYN1	      Was lesion assessed at this visit	            05
101-276	101	TL03	LIVER:LEFT LIVER DOME	            TLYN1	      Was lesion assessed at this visit	            05
101-276	101	TL04	LIVER:RIGHT LIVER DONE	            TLYN1	      Was lesion assessed at this visit	            05
101-276	101	TL01	ABDOMEN:RUQ OMENTAL MET	            TLYN2	      Check if lesion is too small to measure	    06
101-276	101	TL02	ABDOMEN:LEFT ABDOMEN OMENTAL MET	TLYN2	      Check if lesion is too small to measure	    06
101-276	101	TL03	LIVER:LEFT LIVER DOME	            TLYN2	      Check if lesion is too small to measure	    06
101-276	101	TL04	LIVER:RIGHT LIVER DONE	            TLYN2	      Check if lesion is too small to measure	    06
101-276	101	 	 	                                    TLSUM_C	      Sum of Diameters	                            07
101-276	101	 	 	                                    NLDAT	      Date of Assessment	                        08
101-276	101	NL01	LIVER:RIGHT LOBE LIVER LESION	    NLSTAT	      Status	                                    09
101-276	101	NL02	LIVER:LIVER LESIONS	NLSTAT	                      Status	                                    09
101-276	101	NL01	LIVER:RIGHT LOBE LIVER LESION	    NLMEAS	      Measurement	                                10
101-276	101	NL02	LIVER:LIVER LESIONS	                NLMEAS	      Measurement	                                10
101-276	101	 	 	                                    RSDAT	      Date of Assessment	                        11
101-276	101	 	 	                                    RSORRES1	  RECIST 1.1 Target Response	                12
101-276	101	 	 	                                    RSORRES2	  RECIST 1.1 Non-Target Response	            13
101-276	101	 	 	                                    RSYN1	      Were unequivocal new lesions identified	    14
101-276	101	 	 	                                    RSORRES4	  Tumor Biomarker Response	                    15
101-276	101	 	 	                                    TLPCNTCH	  % Change from Baseline	                    16
101-276	101	 	 	                                    TLNADIR	      % Change from NADIR	                        17
101-276	101	 	 	                                    RSORRES3	  RECIST 1.1 Overall Response	                18
;
run;
proc print data=tumor1;run;

The sql step I tried: (it does not create the request dataset though, just add a hint to solve the question):

proc sql;
select *
   from tumor1
   group by sortordernum
   order by sortordernum,id;
quit;