topic Re: Select the least value in the repeated measurements while marking the final contributor in SAS Programming

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

Cruise — Wed, 17 Oct 2018 18:22:46 GMT

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 
;

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

novinosrin — Wed, 17 Oct 2018 16:59:53 GMT

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

Very interesting question indeed as missing killed the min lol 🙂

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

Cruise — Wed, 17 Oct 2018 17:03:47 GMT

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.

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

novinosrin — Wed, 17 Oct 2018 17:03:51 GMT

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

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

Cruise — Wed, 17 Oct 2018 17:04:21 GMT

Hmm, but two right now 🙂

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

novinosrin — Wed, 17 Oct 2018 17:28:13 GMT

Hey how is id 4 both?

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

novinosrin — Wed, 17 Oct 2018 17:40:43 GMT



   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;

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

SuryaKiran — Wed, 17 Oct 2018 17:46:29 GMT

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;

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

novinosrin — Wed, 17 Oct 2018 17:48:29 GMT

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

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

data_null__ — Wed, 17 Oct 2018 17:51:12 GMT

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;

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

Cruise — Wed, 17 Oct 2018 18:03:28 GMT

@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.

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

mkeintz — Wed, 17 Oct 2018 18:18:55 GMT

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;

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

Cruise — Wed, 17 Oct 2018 18:25:28 GMT

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

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

novinosrin — Wed, 17 Oct 2018 19:14:13 GMT

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;

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

Cruise — Wed, 17 Oct 2018 19:21:32 GMT

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

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

novinosrin — Wed, 17 Oct 2018 19:34:30 GMT

While I wholeheartedly love and follow Mark's elegant and fast solutions, I find the above mine suits for lazy people like me and also handles wiiideeee and long datasets conveniently. There isn't much logic here to add my convenience except the diligence of array grouping i.e the 2nd grouping

On the one hand Proc mean does the means for var lists that I don't need to type hence you see double dash and on the other hand the datastep merely computes min for the lists.

Vname merely gets the variable name. No big deal about that.

EDIT: Also, this should handle multiple vars