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
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;
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;
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;
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;
Closest_store = vname(store(whichn(min_dist, of store(*)))); Use the variable that is the result of MIN, not the function.
It is working now, thank you!
Many Thanks!
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.