topic Re: Data Manipulating Sorting in SAS Programming

Data Manipulating Sorting

sijansap — Fri, 20 Apr 2018 02:40:23 GMT

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

Re: Data Manipulating Sorting

Shmuel — Fri, 20 Apr 2018 03:27:09 GMT

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

Re: Data Manipulating Sorting

ChrisNZ — Fri, 20 Apr 2018 03:33:12 GMT

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

Re: Data Manipulating Sorting

sijansap — Fri, 20 Apr 2018 03:48:00 GMT

Thank you ChrisNZ. This works great! I appreciate your help.
-Sijansap

Re: Data Manipulating Sorting

sijansap — Fri, 20 Apr 2018 03:48:16 GMT

Thank you very much Shmuel.
That is exactly what I wanted.