topic Re: Best method for comparing multiple variables in SAS Programming

Best method for comparing multiple variables

supp — Wed, 11 Sep 2019 13:08:21 GMT

Hello SAS community. I am hopeful someone might have a better method for comparing three character variables within the same observations such that it identifies when two or more of the variables are different. Also, a null value in one of the variables should not be seen as different.

hypothetical data:

data have;
infile datalines truncover;
input id $ var1 $ var2 $ var3 $;
datalines;
1 . A A
2 A A A
3 A . B
4 . A B
5 A B C
6 A A .
;

Data want would return ID's 3, 4 and 5 (there is at least one difference between the non missing values). It would not return ID's 1,2 or 6 (all non missing values are the same).

I tried a series of nested if-then do statements. For example:

data want;
set have;
if var1 ne '.' then do;
  if var2 ne '.' then do;
    if var1 ne var2;
  end;
end;
run;

I might have gotten a variation (not exactly like the example above) to maybe work. Given the volume of data it is hard to be confident in my solution. I believe there is a much more elegant solution to this problem, any suggestions would be appreciated.

Re: Best method for comparing multiple variables

novinosrin — Wed, 11 Sep 2019 13:16:22 GMT


data have;
infile datalines truncover;
input id $ var1 $ var2 $ var3 $;
datalines;
1 . A A
2 A A A
3 A . B
4 . A B
5 A B C
6 A A .
;
data want;
set have;
array v var:;
k=cats(of v(*));
if count(k,strip(coalescec(of v(*))))=dim(v)-cmiss(of v(*)) then flag=1;
else flag=0;
drop k;
run;

Re: Best method for comparing multiple variables

PaigeMiller — Wed, 11 Sep 2019 13:27:42 GMT

This is interesting logic, using COALESCEC in a way I would not have though of.

If the real problem allows variables VAR1-VAR3 to take on arbitrary length character strings instead of character strings that have only a single character, I don't think this works any more. Of course, this is me reading between the lines; the original question didn't say that character strings VAR1-VAR3 could be more than one character.

In the more general case, I think you would need some sort of ARRAY to loop over all VAR1-VARn variables, and if a mismatch is found the flag is zero; if a mismatch is not found then the flag is one.

Re: Best method for comparing multiple variables

novinosrin — Wed, 11 Sep 2019 13:44:18 GMT

data have;
infile datalines truncover;
input id $ var1 $ var2 $ var3 $;
datalines;
1 . A A
2 A A A
3 A . B
4 . A B
5 A B C
6 A A .
;

data want;
set have;
array v var:;
array temp(99999)$ _temporary_;
do _n_=1 to dim(v);
 if dim(temp)-cmiss(of temp(*))>0 then if  v(_n_) not in temp then do;
 flag=0;
 return;
 end;
 if v(_n_)>' ' then temp(_n_)=v(_n_);
end;
Flag=1;
run;

Re: Best method for comparing multiple variables

PaigeMiller — Wed, 11 Sep 2019 13:47:24 GMT

data WANT;
set have;
array v var:;
flag=1;
prev_non_missing=v(1);
do i=2 to dim(v);
	if not missing(v(i)) and not missing(prev_non_Missing) and v(i)^=v(i-1) then do;
		flag=0;
		leave;
	end;
	else if not missing(prev_non_missing) and missing(v(i)) then v(i)=prev_non_missing;
	else if missing(prev_non_missing) and not missing(v(i)) then prev_non_missing=v(i);
end;
drop i;
run;

Re: Best method for comparing multiple variables

Tom — Wed, 11 Sep 2019 13:48:03 GMT

You can just take the first non-missing value and then test to see if any other non-missing values exist.

data have;
  infile datalines truncover;
  input id $ var1 $ var2 $ var3 $;
datalines;
1 . A A
2 A A A
3 A . B
4 . A B
5 A B C
6 A A .
7 . . .
;

data want ;
  set have ;
  array var var1-var3 ;
  first_value=coalescec(of var[*]);
  flag=0;
  do _n_=1 to dim(var) while (not flag);
    if not missing(var[_n_]) then flag = first_value ne var[_n_];
  end;
run;

proc print;
run;

                                     first_
Obs    id    var1    var2    var3    value     flag

 1     1              A       A        A         0
 2     2      A       A       A        A         0
 3     3      A               B        A         1
 4     4              A       B        A         1
 5     5      A       B       C        A         1
 6     6      A       A                A         0
 7     7                                         0

Re: Best method for comparing multiple variables

PeterClemmensen — Wed, 11 Sep 2019 13:50:35 GMT

data want;
    set have;
    call sortc(of var:);
    flag=ifn(length(prxchange('s/(.)\1+/$1/', -1, cats(of var:))) > 1, 1, 0);
run;

Re: Best method for comparing multiple variables

Tom — Wed, 11 Sep 2019 14:02:55 GMT

Note that using CALL SORTC() will change the order of the original values.

Also I don't think that method works when the strings are longer than a single character.

Try setting the values to the same multiple character values and see what happens.

Also what value is the use of the IFN() doing in that code?

It looks like you are using it to convert the results of the test for greater than one. So you are using it to convert 1 to 1 and 0 to 0.

Re: Best method for comparing multiple variables

novinosrin — Wed, 11 Sep 2019 14:35:48 GMT

/*Or COUNTW is Simpler*/

data want;
set have;
array v var:;
_k=coalescec(of v(*));
_n=dim(v)-cmiss(of v(*));
_k1=catx(' ',of v(*));
Flag=_n=countw(_k1,strip(_k));
drop _:;
run;

Re: Best method for comparing multiple variables

PeterClemmensen — Wed, 11 Sep 2019 14:19:54 GMT

Ah yes. Once again thank your for the sharp eye @Tom.

The code should rather be

data want;
    set have;
    call sortc(of var:);
    flag=length(prxchange('s/(.)\1+/$1/', -1, cats(of var:))) > 1;
    set have;
run;

This way the order of var1-var3 will be intact in want and the flag is set correctly. Don't need the ifn function.

Of course, this approach will not work if the variables are not single letters no 🙂

Re: Best method for comparing multiple variables

Meghna14 — Wed, 11 Sep 2019 14:15:08 GMT

data have;
infile datalines truncover;
input id $ var1 $ var2 $ var3 $;

datalines;
1 . A A
2 A A A
3 A . B
4 . A B
5 A B C
6 A A .
;
run;

data want;
set have;
array x {3} var1 var2 var3 ;
do i= 1 to 1;
if i ne . then do;
if x{i} ne x{i+1} ne x{i+2} then output;
end;end;
run;

you might modify it as per the amount of variables you have to check .

Re: Best method for comparing multiple variables

gamotte — Wed, 11 Sep 2019 14:26:45 GMT

Hello,

For arbitrary length strings, i think a slightly modified version of @novinosrin's program

should work :

if countw(catx(' ',of v(*)),strip(coalescec(of v(*))))=dim(v)-cmiss(of v(*)) then flag=1;

Re: Best method for comparing multiple variables

ballardw — Wed, 11 Sep 2019 14:41:35 GMT

@supp wrote:

Hello SAS community. I am hopeful someone might have a better method for comparing three character variables within the same observations such that it identifies when two or more of the variables are different. Also, a null value in one of the variables should not be seen as different.

hypothetical data:
data have;
infile datalines truncover;
input id $ var1 $ var2 $ var3 $;
datalines;
1 . A A
2 A A A
3 A . B
4 . A B
5 A B C
6 A A .
;
Data want would return ID's 3, 4 and 5 (there is at least one difference between the non missing values). It would not return ID's 1,2 or 6 (all non missing values are the same).

I tried a series of nested if-then do statements. For example:
data want;
set have;
if var1 ne '.' then do;
  if var2 ne '.' then do;
    if var1 ne var2;
  end;
end;
run;
I might have gotten a variation (not exactly like the example above) to maybe work. Given the volume of data it is hard to be confident in my solution. I believe there is a much more elegant solution to this problem, any suggestions would be appreciated.

You are missing a couple pieces of potentially extremely critical information. Such as, are your character values single characters or longer? And are they something from a fixed list of values (you know what they are expected to be) or just sort of randomly entered text?

Also is this case sensitive? Is "A" equal to "a" for this purpose?

If you have values that are fixed and case sensitive then this approach of creating a custom informat to assign a numeric value will work as what you are in effect requesting is "is the range of values = 0 or not". The following code could be adjusted to case insensitive by adding (UPCASE) after the name of the informat in the Invalue statement of the format. The variable dif will be 1 when there is a difference and 0 otherwise. The numeric values assigned to the values aren't critical as long as each is different though if you have a largish number of possible values a sequential assignment might be easier with a CNTLIN data set for Proc Format.

proc format library=work;
invalue mychars
'A' = 1
'B' = 2
'C' = 3
;
run;

data have;
   infile datalines truncover;
   input id $ var1 $ var2 $ var3 $;
   array vc var1 - var3;
   array vn{3};
   do i=1 to dim(vc);
      vn[i]= input(vc[i],mychars.);
   end;
   drop I vn: ;
   dif = (range(of vn(*)) > 0);
datalines;
1 . A A
2 A A A
3 A . B
4 . A B
5 A B C
6 A A .
;

Note that if you have larger number of variables with the same value list this is easily extendable to more variables. Just increase the array definitions.

Re: Best method for comparing multiple variables

PeterClemmensen — Wed, 11 Sep 2019 15:00:21 GMT

It is easily extendable to handle more than one-character strings though

data have;
  infile datalines truncover;
  input id $ var1 $ var2 $ var3 $;
datalines;
1 .  AB AB
2 AB AB AB
3 AB . BA 
4 .  AB BA
5 AB BA CD
6 AB AB . 
7 .  .  . 
;

data want;
    set have;
    call sortc(of var:);
    flag=countw(prxchange('s/\b(\w+)(?:\W+\1\b)+/$1/', -1, catx(' ', of var:)), ' ') > 1;
    set have;
run;

Re: Best method for comparing multiple variables

supp — Wed, 11 Sep 2019 16:54:16 GMT

Can you elaborate on why the solution wouldn't work for character strings more than one character?

Re: Best method for comparing multiple variables

novinosrin — Wed, 11 Sep 2019 17:19:51 GMT

Hi @supp Requesting you to please test with a small sample. I am intrigued too coz that solution seem not to have anything to do with length rather deals with value. However, I didn't test extensively, and I might later when I have some time.

The logic is simple:

1. concatenate the contents of the array. Cats would have removed leading and trailing blanks

2. Identify the 1st non-missing value with coalescec within the contents of the array

3. dim(v)-cmiss(of v(*)) gives us the number of non-missing elements in the array

4. Logic: Check if the count of the 1st non-missing value is equal to the number of non-missing elements in the array with count function. If yes , the elements are the same, and if false obviously otherwise

5. Hence, i am not sure what has this got to do with length of any sort. Sometimes, i code too fast and I tend to miss out some intricacies, so test and let me know plz.

Re: Best method for comparing multiple variables

supp — Wed, 11 Sep 2019 17:23:18 GMT

Thanks for all the responses. Based on what people posted I read this paper to get a basic understanding of arrays:

https://support.sas.com/resources/papers/proceedings/proceedings/sugi30/242-30.pdf

I applied a couple of the solutions presented here:

Method #1 - find first non missing variable then loop through all (non-missing) variables to check if they have a difference:

data want ;
  set have ;
  array var var1-var3 ;
  first_value=coalescec(of var[*]);
  flag=0;
  do _n_=1 to dim(var) while (not flag);
    if not missing(var[_n_]) then flag = first_value ne var[_n_];
  end;
run;

Method # 2 - Create a new variable with all variables concatenated together. Then count the number of times the first non missing value occurs in the concatenated variable. Compare this count to the total number of non missing variables. If the counts are different then there is a difference among the variables:

data want;
set have;
array v var:;
k=cats(of v(*));
if count(k,strip(coalescec(of v(*))))=dim(v)-cmiss(of v(*)) then flag=1;
else flag=0;
drop k;
run;

I applied both methods to my data, about 35,000 rows, and got the same observations returned. Method #2 seem to have slightly better performance. Less real time, user cpu time, system cpu time and memory used. For this reason method # 2 is my preferred solution.

Re: Best method for comparing multiple variables

hashman — Thu, 12 Sep 2019 03:41:45 GMT

@novinosrin:

Very clever indeed.

However, I can't help but note that CATS in situations like this or similar is dangerous because of its scrambling effect. For example, your solution will readily break on something as simple as:

data have ;                 
  input id (var1-var3) ($) ;
  cards ;                   
1 AB A B                    
;                           
run ;

The obvious reason is that (AB A B) is scrambled to (ABAB), and any algorithm looking for something different from AB will say that all VAR values are equal. Of course, you can modify your code using CATX instead. But there's another caveat: The buffer of any feline function (and also others like REPEAT) is limited to 32767; and so if the sum of the non-blank lengths of the VAR: should exceed 32767, it can break the code because of the truncation.

Methinks in this situation (and situations similar to this), a general solution devoid of the above pitfalls is possible only via array processing following the logic:

(1) Find the first (or last) non-missing value.

(2) See if any subsequent (or preceding) non-missing value is different; and if so, break the loop.

For example:

data have ;                      
  input id (V1-V3) ($) ;      
  cards ;                        
1 .  AB AB                        
2 AA AA AA                        
3 AA .  BB                        
4 .  AA BB                        
5 AA BB CC                        
6 AA AA .                        
7 .  AA .                        
8 .  .  .                        
9 AB A  B
;                                
run ;                            
                                 
data want (drop = _:) ;          
  set have ;                      
  array vv v: ;                  
  do over vv ;                    
    if cmiss (vv) then continue ;
    _n + 1 ;                      
    if _n = 1 then _v = vv ;      
    else if vv ne _v then do ;    
      unequal_flag = 1 ;          
      leave ;                    
    end ;                        
  end ;                          
  unequal_flag = ^^ unequal_flag ;
run ;

It's by far not as elegant as your offering, but it's not going to break on scrambling or insufficient buffer length.

Kind regards

Paul D.

Re: Best method for comparing multiple variables

novinosrin — Thu, 12 Sep 2019 11:50:35 GMT

Guru @hashman Very interesting insight and rationale. Honestly the buffer thing never clicked to me. I guess the tendency of me treating each one like a fun video game rather a real technical solution shows that I lack maturity. 🙂 Nonetheless, that specialist professional demeanor is something I am gradually learning from you.

PS Though both of us live in the same time zone, I request you to kindly bear with delays in me acknowledging your post as it seems I am missing out quite a bit on some threads while I am sleeping and you are wide awake as I noticed this while brushing my teeth this morning. Also you are aware I try to follow your posts in the ones I didn't participate, and indeed the value is priceless.

Re: Best method for comparing multiple variables

Ksharp — Thu, 12 Sep 2019 12:54:44 GMT

data have;
  infile datalines truncover;
  input id $ var1 $ var2 $ var3 $;
datalines;
1 . A A
2 A A A
3 A . B
4 . A B
5 A B C
6 A A .
7 . . .
;
data want;
 if _n_=1 then do;
   length k $ 80;
   declare hash h();
   h.definekey('k');
   h.definedone();
 end;
set have;
array x{*} $ var: ;
do i=1 to dim(x);
  if not missing(x{i}) then do;k=x{i};h.ref(); end;
end;
if h.num_items>1 ;
h.clear();
drop i k;
run;