topic Re: Check if one column contain at least one argument from another column in SAS Programming

Check if one column contain at least one argument from another column

Ronein — Wed, 17 Jan 2024 12:13:29 GMT

Hello

I have a data set with 2 string columns.

I want to create a new column called Ind that will get value 2 or 1 or 0.

IF column W doesn't contain any argument of column X then value 0.

If value of W contain at least one argument from column X but W not equal to X then value 1.

IF W=X then value 2.

What is the way to do it please?

Please note that arguments are determined by delimiter comma

One more condition is that if column X =Column W


Data have;
input X $ w $;
cards;
11,22 33
11,22 44
11,22 11
11,22 22 
11,22 11,22,66
;
run;

Re: Check if one column contain at least one argument from another column

PaigeMiller — Wed, 17 Jan 2024 10:53:56 GMT

Please test your code before posting. Please be sure it produces the desired data set. Testing your code is a good thing to do, and in my opinion, it is mandatory. Please don't make us test it or correct it for you.

Re: Check if one column contain at least one argument from another column

Ronein — Wed, 17 Jan 2024 12:12:45 GMT

Sorry,raw data is


Data have;
input X $ w $;
cards;
11,22 33
11,22 44
11,22 11
11,22 22 
11,22 11,22,66
 ;
run;

Re: Check if one column contain at least one argument from another column

PaigeMiller — Wed, 17 Jan 2024 12:34:04 GMT

Here is my code. I did not program the case where x=w, you did not give us an example in the data set, and I believe you can modify the code to handle this case without our help.

data want;
    set have;
    n_words_x=countw(x,',');
    n_words_w=countw(w,',');
    ind=0;
    do i=1 to n_words_x;
        do j=1 to n_words_w;
            if scan(x,i,',')=scan(w,j,',') then ind=1;
            leave;
        end;
    end;
    drop i j n_words:;
run;

Re: Check if one column contain at least one argument from another column

ballardw — Wed, 17 Jan 2024 15:38:01 GMT

Exactly what is the purpose of storing two or more values in a single variable? This violates principals of data normalization and generally leads to extremely hard to maintain code.

And for further consideration is a value of "11,22" considered equal to "22,11"? See how quickly poor data adds to possibly confusion? By the time you get to 11,22,33 you have to consider "equality" of 11,33,22 22,33,11 22,11,33 33,11,22 33,22,11 . And what about the entirely too likely entry of occasional spaces embedded in the values such as "11, 22"? Is that to be considered equal to "11,22"? (A basic use of the = operator will return false.)