Question about lookup on min values and contributing variables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

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
Dateabcmin
19/08/20144232
18/08/20146676
17/08/201451065
16/08/20141661
15/08/201475105
14/08/20143222
13/08/20149252
12/08/20148787
11/08/20148933
10/08/20142432
After
DateabcminMin var1
19/08/20144232b
18/08/20146676a
17/08/201451065a
16/08/20141661a
15/08/201475105b
14/08/20143222b
13/08/20149252b
12/08/20148787b
11/08/20148933c
10/08/20142432a

Many thanks in advance

Will


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

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;


View solution in original post


All Replies
Super User
Posts: 11,121

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: 19,105

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: 11,121

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: 308

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 387 views
  • 6 likes
  • 4 in conversation