Ammonite | Level 13

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

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
;

``````
1 ACCEPTED SOLUTION

Accepted Solutions
PROC Star

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

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;
``````
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
15 REPLIES 15
Tourmaline | Level 20

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

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

Very interesting question indeed as missing killed the min lol 🙂

Ammonite | Level 13

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

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.

Tourmaline | Level 20

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

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

Ammonite | Level 13

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

Hmm, but two right now 🙂
Tourmaline | Level 20

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

Hey how is id 4 both?

Ammonite | Level 13

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

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.

Tourmaline | Level 20

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

``````

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;``````
Meteorite | Level 14

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

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;``````
Thanks,
Suryakiran
Tourmaline | Level 20

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

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

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;

``````
Ammonite | Level 13

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

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

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

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;
``````
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Tourmaline | Level 20

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

``````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;``````
Ammonite | Level 13