topic Re: cross-data in SAS Programming

Look up smallest value in a row

soumri — Sun, 18 Jun 2017 02:02:31 GMT

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.

Re: cross-data

Reeza — Sun, 18 Jun 2017 00:17:10 GMT

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);

Re: Look up smallest value in a row

Reeza — Sun, 18 Jun 2017 00:21:05 GMT

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.

Re: cross-data

soumri — Sun, 18 Jun 2017 00:31:15 GMT

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.

Re: Look up smallest value in a row

soumri — Sun, 18 Jun 2017 00:34:47 GMT

Yes thank you I guess you're right.

Re: cross-data

Reeza — Sun, 18 Jun 2017 00:58:54 GMT

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/

Re: cross-data

soumri — Sun, 18 Jun 2017 01:03:23 GMT

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

Re: Look up smallest value in a row

Jagadishkatam — Sun, 18 Jun 2017 01:52:28 GMT

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;

Re: Look up smallest value in a row

soumri — Sun, 18 Jun 2017 02:04:49 GMT

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

Re: Look up smallest value in a row

Jagadishkatam — Sun, 18 Jun 2017 02:19:04 GMT

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;

Re: Look up smallest value in a row

soumri — Sun, 18 Jun 2017 02:19:31 GMT

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)

Re: Look up smallest value in a row

Jagadishkatam — Sun, 18 Jun 2017 02:23:17 GMT

Hope you checked the code I posted couple of minutes earlier

Re: Look up smallest value in a row

soumri — Sun, 18 Jun 2017 02:42:35 GMT

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)

Re: Look up smallest value in a row

Reeza — Mon, 19 Jun 2017 14:04:14 GMT

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