DATA Step, Macro, Functions and more

Data Manipulating Sorting

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

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


Accepted Solutions
Solution
‎04-19-2018 11:48 PM
Trusted Advisor
Posts: 1,849

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

 

 

View solution in original post


All Replies
Solution
‎04-19-2018 11:48 PM
Trusted Advisor
Posts: 1,849

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

 

 

Contributor
Posts: 24

Re: Data Manipulating Sorting

Thank you very much Shmuel.
That is exactly what I wanted.
Super User
Posts: 2,516

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
Contributor
Posts: 24

Re: Data Manipulating Sorting

Thank you ChrisNZ. This works great! I appreciate your help.
-Sijansap
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 126 views
  • 1 like
  • 3 in conversation