DATA Step, Macro, Functions and more

Look up smallest value in a row

Reply
Contributor
Posts: 55

Look up smallest value in a row

[ Edited ]

Hi,

I have a data of the form:
Region Sub-region    S1          S2         S3
1              1                20          30          25
1              2                65          47          62
1              3                28          37          59
2              1                58          26          18
2              2                36          45          89
2              3                96          47          35
2              4                19          36          58
3              1                65          45          56
3              2                58          78          89

Regions and sub-regions are alphanumeric, S1, S2 and S3 are measurement antennas. The data are distances.
I want to have in front of each subregion the name of the nearest Antenna (S1, or S2 or S3) and the distance that corresponds to it. The result will be as follows:
Region Sub-region    S1          S2         S3   Nearest      Distance
1              1                20          30          25        S1              20         
1              2                65          47          62        S2              47
1              3                28          37          59        S1              28
2              1                58          26          18        S3              18
2              2                36          45          89        S1              36
2              3                96          47          35        S3              35
2              4                19          36          58        S1              19
3              1                65          45          56        S2              45
3              2                58          55          89        S2              55
My database contains 177 different sub-regions and 7 measurement antennas (S1, .... S7).
Thank you for helping me.

Super User
Posts: 17,865

Re: cross-data

Functions time. 

SMALLEST()/MIN() -> identify the lowest value

VNAME() -> name of the variable

WHICHN() -> which item in array holds the value indicated, note that if there are multiples the first is returned. 

 

If the station list is numeric as indicated, rather than use VNAME you can directly concatenate the index to the word station.

 

array _stations(3) station1-station3;

nearest = min(of _stations(*)); *smallest value;
index = whichn(nearest, of _stations(*)); *index of smallest value;
Station_ID = vname(_stations(index); *Name of variable;

Station_ID2 = "Station"||put(index, 2. -l);
Contributor
Posts: 55

Re: cross-data

Thanks for your quick reply.
It may be that your proposal will not work with my old SAS version (V 9.0) and this is probably due to the WHICHN function (unfortunately). Can you confirm it to me.

Super User
Posts: 17,865

Re: cross-data

[ Edited ]

No...SAS 9.0 is ancient, a decade old at least (EDIT: released in 2002). Upgrades are free with a valid license. 

 

You're stuck looping through your array then until you find the minimum value. I'd still find the min using the MIN function though and then loop until you find it. 

https://stats.idre.ucla.edu/sas/seminars/sas-arrays/

 

 

 

 

Contributor
Posts: 55

Re: cross-data

Yes I do not have problem to have the Minimum value but it is the assignment of the name of the corresponding station that gets me the most

Super User
Posts: 17,865

Re: Look up smallest value in a row

Just a quick note that I updated the subject line to be more reflective of your question. This helps for when future users are searching for an answer to their question.

Contributor
Posts: 55

Re: Look up smallest value in a row

Yes thank you I guess you're right.

Trusted Advisor
Posts: 1,131

Re: Look up smallest value in a row

This is an alternative way with arrays and do loop

 

data want;
length distance $10;
set have;
array s(*) s1-s3;
nearest=min(of s(*));
do i = dim(s) to 1 by -1;
if s(i)=nearest then distance=vname(s(i));
end;
run;
Thanks,
Jag
Contributor
Posts: 55

Re: Look up smallest value in a row

There was a fault in my post I corrected it (that's because I'm not trying to rename Station1 by S1).

Trusted Advisor
Posts: 1,131

Re: Look up smallest value in a row

so the variable names with station remain same, by you want the nearest variable to have values S1,S2..... then you could try 

 

data want;
length distance $10;
set have;
array s(*) station1-station3;
nearest=min(of s(*));
do i = dim(s) to 1 by -1;
if s(i)=nearest then distance='S'||compress(vname(s(i)),,'kd');
end;
run;
Thanks,
Jag
Contributor
Posts: 55

Re: Look up smallest value in a row

This gives the minimum distance among the columns but does not give the name of the station that corresponds to it (S1 or S2 or S3)

Trusted Advisor
Posts: 1,131

Re: Look up smallest value in a row

Hope you checked the code I posted couple of minutes earlier
Thanks,
Jag
Contributor
Posts: 55

Re: Look up smallest value in a row

Yes of course. There is a fault of which here is the log:
If s (i) = nearest then distance = 'S' | compress (vname (s (i)) ,, 'kd');
                                                                                                     -
                                                                                                   159
ERROR 159-185: Null parameters for COMPRESS are invalid.

(It concerns the second comma)

Super User
Posts: 17,865

Re: Look up smallest value in a row

You have one bar, not two as in code provided. I believe one bar is OR not the concatenare operator. 

 

 

Ask a Question
Discussion stats
  • 13 replies
  • 369 views
  • 4 likes
  • 3 in conversation