Obsidian | Level 7

## Data Manipulating Sorting

Dear experts,

I have 3 columns of 3-digit data with about 30k rows. I want to concatenate and put commas between them to make a single character variable and use PROC FREQ to get the count of each of the similar combinations. The problem is I want to treat, say ABC is same as ACB, or BAC, etc. (all 6 possible combination same) as same entry and if all 6 combinations are present in the data then PROC FREQ should yield ABC with freq count 6. Also, if there are only two A and B the treat AB and BA same. The data looks like:

DATA HAVE;

input A B C;

D= CATT(of A, B, C);

cards;

801 905 823

903 801 803

905 801 823

823 801  .

823 905 801

801 823 .

...;

Now D will have 1st, 3rd, and 5th row will have (801,905,823), (905,801,823) and (823,905,801), I would like PROC FREQ to yield (801, 823, 905) with frequency 3, And, similarly from 4th and 6th row (801,823) with frequency 2.

I will appreciate any suggestions.

Thank you all.

Sijansap

1 ACCEPTED SOLUTION

Accepted Solutions
Garnet | Level 18

## Re: Data Manipulating Sorting

Try next code:

``````DATA HAVE;
input A \$ B \$  C \$;
array v \$ A B C;
call sortc(v);
a = v(1);
b = v(2);
c = v(3);
length D \$11;
D = catx(',',A,B,C);
cards;
801 905 823
903 801 803
905 801 823
823 801  .
823 905 801
801 823
;run
``````

4 REPLIES 4
Garnet | Level 18

## Re: Data Manipulating Sorting

Try next code:

``````DATA HAVE;
input A \$ B \$  C \$;
array v \$ A B C;
call sortc(v);
a = v(1);
b = v(2);
c = v(3);
length D \$11;
D = catx(',',A,B,C);
cards;
801 905 823
903 801 803
905 801 823
823 801  .
823 905 801
801 823
;run
``````

Obsidian | Level 7

## Re: Data Manipulating Sorting

Thank you very much Shmuel.
That is exactly what I wanted.
Tourmaline | Level 20

## Re: Data Manipulating Sorting

Like this?

``````data HAVE;
input A B C;
call sortn(A,B,C);
D= catx(',', A, B, C);
cards;
801 905 823
903 801 803
905 801 823
823 801  .
823 905 801
801 823 .
run;
proc freq;
table D;
run;``````
D Frequency Percent Cumulative
Frequency
Cumulative
Percent
.,801,823 2 33.33 2 33.33
801,803,903 1 16.67 3 50.00
801,823,905 3 50.00 6 100.00
Obsidian | Level 7

## Re: Data Manipulating Sorting

Thank you ChrisNZ. This works great! I appreciate your help.
-Sijansap
Discussion stats
• 4 replies
• 1000 views
• 1 like
• 3 in conversation