topic Re: compare multiple columns and concat in SAS Programming

compare multiple columns and concat

erdem_ustun — Fri, 21 Apr 2017 17:37:38 GMT

Hi,

I have a sample dataset with the following columns.
To compare all the columns
I want to do comparison with the data step.
Values can be empty or string or numeric

data have;
informat id v1-v12 $8.;
input id v1-v12;
cards;
1 1 1 1 3 a b . 4 x . 2 2
2 4 5 h k L L A a 4 6 7 c
3 3 4 5 6 7 3 6 8 8 a w d
4 6 7 2 1 1 2 3 4 5 6 7 s
;

There are data for 12 months.I want to compare them in order.

cats(ifc(v1=v2,'0','1')
cats(ifc(v2=v3,'0','1')
cats(ifc(v3=v4,'0','1')
cats(ifc(v4=v5,'0','1')
cats(ifc(v5=v6,'0','1')
cats(ifc(v6=v7,'0','1')
cats(ifc(v7=v8,'0','1')
cats(ifc(v8=v9,'0','1')
cats(ifc(v9=v10,'0','1')
cats(ifc(v10=v11,'0','1')
cats(ifc(v11=v12,'0','1')

At the end, a concatenated string will be formed.

For id = 1 concat_compare='0;0;1;1;1;1;1;1;1;1;0' and

same_count=7 /* I have 3 item for 1, 2 item for 2 and 2 item for missing ,sum(3,2,2) */
not_same_count=5

same_values=1;2; .; /*distinct values, Although it is not the same but more than one / If empty record exists it must be listed */

not_same_values=3;a;b;4;x /*distinct values only have one */

I found a code to find the number of the same on the forum.

data want (drop=i j);
set have;
array vars $8. v1-v12;
same=0;
do i=1 to dim(vars)-1;
do j=i+1 to dim(vars);
same=sum (same, vars(i) eq vars(j));
end;
end;
run;

But I could not do the others.

I need the following fields on a record-based.

concat_compare same_count not_same_count same_values not_same_values

Is it possible to do this for each record/id?

Best regards

Re: compare multiple columns and concat

RW9 — Fri, 21 Apr 2017 14:44:07 GMT

Well, your concat problem is simple and well explained, so code below. For your other four variables however, I didn't follow at all - please clarify.

data have;
informat id v1-v12 $8.;
input id v1-v12;
cards;
1 1 1 1 3 a b . 4 x . 2 2
2 4 5 h k L L A a 4 6 7 c
3 3 4 5 6 7 3 6 8 8 a w d
4 6 7 2 1 1 2 3 4 5 6 7 s
;
run;

data want;
  set have;
  length concat_compare $2000;
  array vals{*} v:;
  do i=1 to dim(vals)-1;
    concat_compare=catx(';',concat_compare,ifc(vals{i}=vals{i+1},"1","0")); 
  end;
run;

Re: compare multiple columns and concat

ballardw — Fri, 21 Apr 2017 15:20:18 GMT

for Id 1 V3=1 and v4=3. So wouldn't the result be

0;0;1;1;1;1;1;1;1;1;0

and not your example

0;0;0;1;1;1;1;1;1;1;0

Re: compare multiple columns and concat

erdem_ustun — Fri, 21 Apr 2017 16:40:51 GMT

Yes, Yes, I made a mistake.Just like you wrote it.

0;0;1;1;1;1;1;1;1;1;0

thanks

Re: compare multiple columns and concat

erdem_ustun — Fri, 21 Apr 2017 17:34:20 GMT

Hi RW9;

I made the mistake of including id in my first post.

thanks for your reply.I will try to explain the other four variables.

mean of same_values :It is a list of multiple recurring values when we accept each record as an array. so for id=1 ,

The same_values will be "1;2; . "

same_values="1;2; . " (missing is include)

mean of not_same_values:A list of values that do not repeat more than once when we accept each record as an array.

so for id=1 , The not_same_values will be "3;a;b;4;x"

not_same_values="3;a;b;4;x"

same_count:The number of times that multiple repeating values are repeated. It is the sum of the number of repeats

for example ;

for id = 1 , Repeated 1 to 3 times,Repeated missing value(.) to 2 times Repeated 2 to 2 times and Their sum is 7 (3+2+2)

same_count=7

not_same_count:The number of variables that do not repeat more than once

for id = 1, Repeated 3 to 1 times,

Repeated a to 1 times,

Repeated b to 1 times,

Repeated 4 to 1 times,

Repeated x to 1 times

and Their sum is 5 (1+1+1+1+1)

not_same_count=5

I hope,It is more descriptive.

Best regards