BookmarkSubscribeRSS Feed
soumri
Quartz | Level 8

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.

13 REPLIES 13
Reeza
Super User

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);
soumri
Quartz | Level 8

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.

Reeza
Super User

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/

 

 

 

 

soumri
Quartz | Level 8

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

Reeza
Super User

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.

soumri
Quartz | Level 8

Yes thank you I guess you're right.

Jagadishkatam
Amethyst | Level 16

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
soumri
Quartz | Level 8

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

Jagadishkatam
Amethyst | Level 16

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
soumri
Quartz | Level 8

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)

Jagadishkatam
Amethyst | Level 16
Hope you checked the code I posted couple of minutes earlier
Thanks,
Jag
soumri
Quartz | Level 8

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)

Reeza
Super User

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

 

 

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
  • 13 replies
  • 1550 views
  • 4 likes
  • 3 in conversation