BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
VVill
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Loko
Barite | Level 11

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

7 REPLIES 7
ballardw
Super User

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;

Reeza
Super User

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;

VVill
Calcite | Level 5

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;

ballardw
Super User

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

VVill
Calcite | Level 5

It is working now, thank you!

Loko
Barite | Level 11

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;


sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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