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.
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);
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.
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/
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
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.
Yes thank you I guess you're right.
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;
There was a fault in my post I corrected it (that's because I'm not trying to rename Station1 by S1).
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;
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)
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)
You have one bar, not two as in code provided. I believe one bar is OR not the concatenare operator.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.