Select the least value in the repeated measurements while marking the final contributor

[Cruise]

Hi ya'll, 

I have number of individuals with repeated measurements with values of proc_range and dx_range. I'm interested in knowing which range variable has the least value across all measurements among these two variables proc_range and dx_range and mark which one was selected as the least in value. The problem here is also a missing in range variables. Because missing here is what its is and I would just go ahead with the variable with value available.  

Thanks for any help. I appreciate your time! 

data have; 
input id proc_range dx_range;
cards;
1	12	9
1	8	12
1	6	8
1	6	8
1	5	14
1	5	11
1	5	8
1	5	.
1	5	13
2	7	0
2	8	0
2	14	0
2	13	0
2	11	0
3	8	7
3	0	.
3	7	7
4	1	5
4	1	.
4	1	7
; 


data want; 
input id range source;
cards;
1	5	proc
2	0	dx
3	0	proc
4	1	proc 
;

[novinosrin]

Do you just have 2 vars to check or there could be many?

Very interesting question indeed as missing killed the min lol 🙂

[Cruise]

Yes, missing always makes our life more challenging and the problems more interesting? hehe. Thank god just two variables here. However, it can be many in the future. But two for the problem so far.

[novinosrin]

" However, it can be many in the future"  Hmm 

[Cruise]

Hmm, but two right now 🙂

[novinosrin]

Hey how is id 4 both?

[Cruise]

@novinosrin@data_null__@SuryaKiran

guys, thanks you so much. WANT data is just to give an idea what I'm trying to achieve. 

I need to mark the minimum of proc_range and dx_range and if proc_range and dx_range contributes the same value then mark as 'both' contributed to the minimum value. 

as simple as this, until it hits missing. 

data WANT is the information I'm looking for not that final dataset has to look like exactly. 

[novinosrin]

   data have; 
input id proc_range dx_range;
cards;
1	12	9
1	8	12
1	6	8
1	6	8
1	5	14
1	5	11
1	5	8
1	5	.
1	5	13
2	7	0
2	8	0
2	14	0
2	13	0
2	11	0
3	8	7
3	0	.
3	7	7
4	1	5
4	1	.
4	1	7
; 

proc sql;
create table temp as
select ID, min(proc_range) as min_proc,min(dx_range) as min_dx
from have
group by id;
quit;
data want;
merge have temp;
by id;
length source $32;
retain source;
array j(*) proc_range dx_range;
R=min(min_proc,min_dx);
if min_proc=min_dx then source='BOTH';
else do;
_iorc_=whichn(R,proc_range,dx_range);
if _iorc_>0 then source=vname(j(_iorc_));
end;
if last.id;
keep id source r;
run;

[SuryaKiran]

If ignoring missing then how did you get both for the last record?

You might need to change the expression in IFN and IFC to meet your requirements.

proc sql;
select pr.id, ifn(proc<dx,proc,dx) as range,   /* You can change the expression here */
			  ifc(proc<dx,"proc","dx") as source
	from (select id,min(Proc_range) as Proc
			from have
			where Proc_range is not null
			group by id) as pr
left join (select id,min(dx_range) as dx
			from have
				where dx_range is not null
				group by id
			) as dx
	on pr.id=dx.id;
quit;

[novinosrin]

Since you need source information Proc sql suggestion is actually out of scope for the reason vname is not supported in proc sql nor is array processing when I believe the complexity will eventually arise as the number of vars increases

[data_null__]

Does this produce the an intermediate to your want data.  I don't really understand your WANT data.

1365  ;;;;
1366     run;
1367  proc print;
1368     run;

The SAS System                                                                          06:53 Wednesday, October 17, 2018 130

             proc_
Obs    id    range    dx_range

  1     1      12         9   
  2     1       8        12   
  3     1       6         8   
  4     1       6         8   
  5     1       5        14   
  6     1       5        11   
  7     1       5         8   
  8     1       5         .   
  9     1       5        13   
 10     2       7         0   
 11     2       8         0   
 12     2      14         0   
 13     2      13         0   
 14     2      11         0   
 15     3       8         7   
 16     3       0         .   
 17     3       7         7   
 18     4       1         5   
 19     4       1         .   
 20     4       1         7   
NOTE: There were 20 observations read from the data set WORK.HAVE.

1369  proc summary nway missing;
1370     class id;
1371     output out=test
1372        idgroup(min(proc_range) missing out(proc_range)=)
1373        idgroup(min(dx_range) missing out(dx_range)=)
1374        ;
1375     run;

NOTE: There were 20 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.TEST has 4 observations and 5 variables.

1376  proc print;
1377     run;

The SAS System                                                                          06:53 Wednesday, October 17, 2018 131

                                 proc_
Obs    id    _TYPE_    _FREQ_    range    dx_range

 1      1       1         9        5          .   
 2      2       1         5        7          0   
 3      3       1         3        0          .   
 4      4       1         3        1          .   
NOTE: There were 4 observations read from the data set WORK.TEST.

1378  proc printto;
1379     run;

[Cruise]

Thanks. WANT data is edited now. Sorry for a confusion.

[mkeintz · Accepted solution]

Data sorted by ID?   Eschew proc sql:

data have; 
input id proc_range dx_range;
cards;
1	12	9
1	8	12
1	6	8
1	6	8
1	5	14
1	5	11
1	5	8
1	5	.
1	5	13
2	7	0
2	8	0
2	14	0
2	13	0
2	11	0
3	8	7
3	0	.
3	7	7
4	1	5
4	1	.
4	1	7
; 
data want (keep=id min source);
  set have;
  by id;
  array mins {2} _temporary_;
  if first.id then call missing(of mins{*});

  array src {2} $5 _temporary_ ('proc','dx');
  array vals {2} proc_range dx_range;
  do i=1 to 2;
    mins{i}=min(mins{i},vals{i});
  end;
  if last.id;
  min=min(of mins{*});
  if mins{1}=mins{2} then source='both';
  else source=src{whichn(min,of mins{*})};
run;

[novinosrin]

data have; 
input id proc_range dx_range;
cards;
1	12	9
1	8	12
1	6	8
1	6	8
1	5	14
1	5	11
1	5	8
1	5	.
1	5	13
2	7	0
2	8	0
2	14	0
2	13	0
2	11	0
3	8	7
3	0	.
3	7	7
4	1	5
4	1	.
4	1	7
; 



PROC MEANS DATA=have NOPRINT NWAY;
CLASS id;
VAR proc_range--dx_range;
OUTPUT OUT=temp(drop=_:) min=;
RUN;

data want;
set temp;
array t(*) proc_range--dx_range;
array j(*) proc_range--dx_range min;
length source $20;
min=min(of t(*));
if range(of j(*))=0 then source='BOTH';
else do;
_t=whichn(min,of t(*));
if _t>0 then source=vname(t(_t));
end;
keep id min source ;
run;

[Cruise]

I have to learn more about this vname function. Appears to be very helpful one. Right?