Please improve call sortc/sortn procedures and vname function

[Shmuel]

I had an idea how to find variables with equal values in an array, as posted in

https://communities.sas.com/t5/SAS-Programming/Values-the-same/m-p/663839/highlight/false#M198244

by using call sortc / sortn procedures and vname function. 

While running the code I found out some issues, which I hope will be solved in a future release. 

1) Next code results in syntax error. I believe it should be improved:

array _c{*} _character_;
array _n{*} _numeric_;

/* next lines create syntax error */
call sortN(_n(*));
call sortC(_c(*));

2) Having an array, VNAME does not take in account the fact that the array was sorted

    and may result with a wrong variable name, different from the original:

/* suppose var1=20,  var2=10, var3=15 */
array _n {3} var1 var2 var3;
call sortn(var1, var2, var3);

/* after sort array values are in order : 10, 15, 20 i.e. var2 var3 var1 */
min_value = vname(_n(1)); /* will result with var1  instead the expected variable name var2 (=10) */

An email was sent to the SAS Technical Support (support@sas.com).

Track number: SAS 7613112314

[PGStats]

For 1) add the "of" keyword

call sortN(of _n(*));
call sortC(of _c(*));

[PGStats]

2) would require a major change in the semantics of data step arrays. I.e., after a call to sortc or sortn the assignment of array positions to datastep variables would be changed in a way that would depend on the data values. The only way to know which is which would be to call vname. This might lead to very intricate programming issues.

[ChrisNZ]

This looks like expected behaviour to me.

So your code yields that VAR1 is the minimum value. Well, that is true. VAR1=10 after the call sort.

The variables are not moved around. Only the values are.

[Shmuel]

@ChrisNZ, suppose the array represent monthly income or test results of different medicines, would it be logic to change values of variables just because sorting the array by value?

[ChrisNZ]

@Shmuel 

> would it be logic to change values of variables just because sorting the array by value?

Well that's exactly what this sorting function does: change the values of the variable. So yes.

The behaviour you are requesting is not implemented by any function. If it were, it would be a different function.

This one does what's on the tin, and requesting a different behaviour means requesting a different function.

[PGStats]

Since lots of SAS programs are already built around the current behaviour of arrays and variable list functions, I think it would be more realistic to either request new functions or find alternate ways to work with existing behaviours. For example, to identify the variable which contains the second smallest value in a numeric array, you can do:

secondSmallestVar = vname(myArray{ whichn(smallest(2,  of myArray), of myArray) });

it's a bit awkward, but not the kind of thing you need to do every day.

[ChrisNZ]

you need something like this:

data T;
  retain VAR1 10 VAR2 5 VAR3 10 VAR4 8;

  array _BEFORE{4} VAR1-VAR4;
  array _AFTER {4} _temporary_;
  array _NAMES {4} $32 _temporary_;

  * You could make a copyarray function, like the SCL function;
  do I=1 to 4;
    _AFTER[I]=_BEFORE[I];
  end;

  call sortn(of _AFTER[*]);

  * You could make a findsortedvar function out of this;
  do I=1 to 4;
    POS=whichn(_AFTER[I], of _BEFORE[*]);
    _NAMES[I] =vname(_BEFORE[POS]);
    _BEFORE[POS]=9999;
    put _NAMES[I]= ;
  end;  

run;

_NAMES[1]=VAR2
_NAMES[2]=VAR4
_NAMES[3]=VAR1
_NAMES[4]=VAR3

[ChrisNZ]

A loop using smallest() as suggested by @PGStats is probably a better solution than mine, if you want to create your own function.

[Shmuel]

Dear @ChrisNZ and @PGStats , thanks for this discussion.

You have convinced me that - 

1) syntax of call sortn / sortc is OK and it was my misuse.

2) there is some logic to current sort functionality.

3) using two variables instead a variable name and its value, 

    enables all kind of analysis, and no need for a new sort function.

Thanks, again

Shmuel

[Shmuel · Accepted solution]

Finally I created a code, similar to @ChrisNZ 's which started with:

data T;
  retain VAR1 10 VAR2 5 VAR3 10 VAR4 8;

  array _BEFORE{4} VAR1-VAR4;
  array _AFTER {4} _temporary_;
  array _NAMES {4} $32 _temporary_;

where function used whichn may have malfunction in case of two or more variables have the same value.

Here is  my tested code for numeric array, which I believe is easy to adapt to char type array.

data tst;
     dlm = '10'x; /* any none expected character in values may be used as delimiter */
     fmt='z7.2';  /* format used to have correct alignment of numeric values */
     lenght value 8; /* force value to be a numeric variable */

     var1=20; var2=5; var3=10;
     array _NAMES {3} $10. ('var1' 'var2' 'var3');
     array _BEFORE {*} var1-var3;
     array _temp  {*} $ _tmp1-_tmp3;
     
     /* each temporary member contains the value, a delimiter and index to the names array */
     do i=1 to dim(_BEFORE);
        _temp(i) = catx(dlm , putn(_BEFORE(i),fmt), i );
     end;
     call sortn(of _temp(*));
     
     do i=1 to dim(_temp);
        value = scan(_temp(i),1, dlm);
        ix_name = scan(_temp(i),2, dlm);
        varname = _NAMES(ix_name);
        put i= varname= value=;
     end;
run;

results into:

i=1 varname=var2 value=5

i=2 varname=var3 value=10

i=3 varname=var1 value=20

[s_lassen]

I think this problem is better solved using hashes. Given this data:

data have;
  input a b c d k;
cards;
1 4 2 7 5
4 7 4 2 56
3 5 44 4 4 
;run;

you can get the names and indexes of variables like this:

data _null_;
  set have;
  array arr(*) _numeric_;
  length name $32 index value 8;
  if _N_=1 then do;
    declare hash h(multidata: 'YES',ordered: 'A');
    h.definekey('value');
    h.definedata('name','index','value');
    h.definedone();
    declare hiter iter('h');
    end;
  h.clear();
  do index=1 to dim(arr);
    value=arr(index);
    name=vname(arr(index));
    h.add();
    end;
  put _N_=;
  rc=iter.first();
  do until(iter.next());
    put name= index= value=;
    end;
run;

Of course, it may be a bit of a waste to put all the names into the hash as well, but you can also drop them from the hash and get them using VNAME(ARR(INDEX)).

[Shmuel]

@s_lassen, any program can be written in many ways and with different methods.

The code you presented by using HASH method works fine, but you missed the point

I had presented - using call sortN (or sortC) moves values among variables, thus after sort the vname function shows the current variable name instead the original variable name.

Both programs, @ChrisNZ and mine uses call sortN but overcomes the issue by using two more arrays (as you have done in your code) - the variable names array and the original index array (in my code it is in the temporary array having concatenated value and index and a delimiter between).

Anyway, thanks for providing a new solution.

[ballardw]

---

@Shmuel wrote:

@ChrisNZ, suppose the array represent monthly income or test results of different medicines, would it be logic to change values of variables just because sorting the array by value?

---

The only way I see this happening, at least without a very explicit concrete example, is a programmer not watching the declared properties of variables, and probably more likely with character. Since the length of a variable can't change after it is created then using different length variables in an array and then sorting will "change values" because the programmer did not consider the results.

If you need to sort arrays then making sure that every single element of the array will hold any given value is, IMHO, the responsibility of the programmer.

Which may mean creating a second array of uniform characteristics.

Or possibly make sure when your variables are first created that they all have the proper length.. I.E. read the data with a well designed data step into a variable long enough to hold the longest possible value.

If the users won't tell you that length then warn them that without sufficient information their data may get truncated in the future because of poor planning and data standards.