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
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
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
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 |
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.