## Question about lookup on min values and contributing variables

Solved
Occasional Contributor
Posts: 12

# Question about lookup on min values and contributing variables

Hi all,

I need to transfer millions rows of data in below format and the key challenge is I need to produce a lookup based on the min value(s) that I found and perform a lookup upon them to get a valuable that has the minimum value.

 Before Date a b c min 19/08/2014 4 2 3 2 18/08/2014 6 6 7 6 17/08/2014 5 10 6 5 16/08/2014 1 6 6 1 15/08/2014 7 5 10 5 14/08/2014 3 2 2 2 13/08/2014 9 2 5 2 12/08/2014 8 7 8 7 11/08/2014 8 9 3 3 10/08/2014 2 4 3 2 After Date a b c min Min var1 19/08/2014 4 2 3 2 b 18/08/2014 6 6 7 6 a 17/08/2014 5 10 6 5 a 16/08/2014 1 6 6 1 a 15/08/2014 7 5 10 5 b 14/08/2014 3 2 2 2 b 13/08/2014 9 2 5 2 b 12/08/2014 8 7 8 7 b 11/08/2014 8 9 3 3 c 10/08/2014 2 4 3 2 a

Will

Accepted Solutions
Solution
‎08-21-2014 08:41 AM
Super Contributor
Posts: 319

## Re: Question about lookup on min values and contributing variables

Hello,

You can also extract all the variable names with the minimum values:

data Before ;
input Date ddmmyy10. a b c min ;
datalines;
19/08/2014 4 2 3 2
18/08/2014 6 6 7 6
17/08/2014 5 10 6 5
16/08/2014 1 6 6 1
15/08/2014 7 5 10 5
14/08/2014 3 2 2 2
13/08/2014 9 2 5 2
12/08/2014 8 7 8 7
11/08/2014 8 9 3 3
10/08/2014 2 4 3 2
;

data want;
set before;
length minvar1 \$ 6;
array intvars{3} \$ a b c ;
array varx(3) \$ _temporary_ ('a' 'b' 'c');
do i=1 to hbound(intvars);
if intvars{i}=min then
do;
put minvar1=;
minvar1=cats(minvar1,",",varx{i});
*if you want the first variable with the minimum value just uncomment the following line;
*leave;
end;
end;

minvar1=substr(minvar1,2);
run;

All Replies
Super User
Posts: 13,584

## Re: Question about lookup on min values and contributing variables

data want;

set have;

min= min(a,b,c);

vnum = whichn(min,a,b,c);

/* there's probably slicker ways but this gives an idea*/

if vnum=1 then MinVar1='a';

else if vnum=2 then MinVar1='b';

else if vnum=3 then MinVar1='c';

drop vnum;

run;

Super User
Posts: 23,787

## Re: Question about lookup on min values and contributing variables

If you don't care about ties then something like the following will work, mainly using the whichn and vname function with an array.

You can always loop through as well and use the vname option:

data want;

set have;

array vars(3) a b c;

min=min(of vars(*));

min_var=vname(vars(whichn(min, of vars(*))));

run;

Occasional Contributor
Posts: 12

## Re: Question about lookup on min values and contributing variables

I have tried it on the big dataset, but it crashed into error:

data set3;

set set2;

array store (*) dist_A--dist_Y;

Min_dist=min(of store(*));

/*It works until this point*/

*Is this formula below that is not working;

Closest_store = vname(store(whichn(min, of store(*))));

Please can you give me a hand on this?

Many thanks again!

Will

run;

Super User
Posts: 13,584

## Re: Question about lookup on min values and contributing variables

Closest_store = vname(store(whichn(min_dist, of store(*)))); Use the variable that is the result of MIN, not the function.

Occasional Contributor
Posts: 12

## Re: Question about lookup on min values and contributing variables

It is working now, thank you!

Occasional Contributor
Posts: 12

## Re: Question about lookup on min values and contributing variables

Many Thanks!

Solution
‎08-21-2014 08:41 AM
Super Contributor
Posts: 319

## Re: Question about lookup on min values and contributing variables

Hello,

You can also extract all the variable names with the minimum values:

data Before ;
input Date ddmmyy10. a b c min ;
datalines;
19/08/2014 4 2 3 2
18/08/2014 6 6 7 6
17/08/2014 5 10 6 5
16/08/2014 1 6 6 1
15/08/2014 7 5 10 5
14/08/2014 3 2 2 2
13/08/2014 9 2 5 2
12/08/2014 8 7 8 7
11/08/2014 8 9 3 3
10/08/2014 2 4 3 2
;

data want;
set before;
length minvar1 \$ 6;
array intvars{3} \$ a b c ;
array varx(3) \$ _temporary_ ('a' 'b' 'c');
do i=1 to hbound(intvars);
if intvars{i}=min then
do;
put minvar1=;
minvar1=cats(minvar1,",",varx{i});
*if you want the first variable with the minimum value just uncomment the following line;
*leave;
end;
end;

minvar1=substr(minvar1,2);
run;

🔒 This topic is solved and locked.