topic Re: Question about lookup on min values and contributing variables in SAS Programming

Question about lookup on min values and contributing variables

VVill — Tue, 19 Aug 2014 16:32:22 GMT

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

Many thanks in advance

Will

Re: Question about lookup on min values and contributing variables

ballardw — Tue, 19 Aug 2014 16:46:32 GMT

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;

Re: Question about lookup on min values and contributing variables

Reeza — Tue, 19 Aug 2014 16:50:59 GMT

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;

Re: Question about lookup on min values and contributing variables

VVill — Tue, 19 Aug 2014 17:57:28 GMT

Many Thanks!

Re: Question about lookup on min values and contributing variables

VVill — Wed, 20 Aug 2014 14:37:21 GMT

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;

Re: Question about lookup on min values and contributing variables

ballardw — Wed, 20 Aug 2014 14:58:47 GMT

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

Re: Question about lookup on min values and contributing variables

VVill — Wed, 20 Aug 2014 15:05:41 GMT

It is working now, thank you!

Re: Question about lookup on min values and contributing variables

Loko — Thu, 21 Aug 2014 12:41:28 GMT

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;