Adding Frequencies

[yoyong]

I did a proc freq on a large data set and the output looked similar to this.

Stenosis                   Frequency     

1                                     70 

1,2                                  12 

1,3                                    8 

2,4,5                               15

4,5                                  16

5                                       7

The Stenosis variable has 5 options and respondents can choose multiple answers.

How do I add the total number of respondents who chose "1", "2", etc. using SAS?

My output should look like

Stenosis             Frequency

1                                 90 

2                                 27

3                                  8

4                                 31

5                                 38

Thanks in advance for your responses.

[novinosrin]

data have;
input Stenosis   $                Frequency     ;
cards4;
1                                     70 
1,2                                  12 
1,3                                    8 
2,4,5                               15
4,5                                  16
5                                       7
;;;;
data temp;
set have end=last;
do _n_=1 to countw(Stenosis,',');
_k=scan(Stenosis,_n_,',');
output;
end;
run;
proc sql;
create table want as
select _k as Stenosis,sum(Frequency) as freq
from temp
group by _k;
quit;

[novinosrin]

@yoyong If you want a one step solution using hashes, very much possible. If you really want that, let us know. But I do think syntactically and logically the above is very easy to handle

[Reeza · Accepted solution]

Separate your raw data and then run a PROC FREQ on it. 

You can do that using SCAN() and COUNTW().

data have;
input @1 Stenosis $9.  @10  Frequency  ;
cards; 
1        70 
1,2      12 
1,3      8 
2,4,5    15
4,5      16
5        7
;;;;
run;

data expanded;
set have;
    *count the number of words in the string;
    n_words = countw(stenosis);

    *create a record for each word;
    do i=1 to n_words;
        Word = scan(stenosis, i, ','); *extracts the word of interest;
        output; *outputs to data set;
    end;
    keep stenosis word frequency n_words; *keep only these variables in final data set;
run;

proc freq data=expanded;
table word;
weight frequency;
run;

---

@yoyong wrote:

I did a proc freq on a large data set and the output looked similar to this.

Stenosis                   Frequency     

1                                     70 

1,2                                  12 

1,3                                    8 

2,4,5                               15

4,5                                  16

5                                       7

The Stenosis variable has 5 options and respondents can choose multiple answers.

How do I add the total number of respondents who chose "1", "2", etc. using SAS?

My output should look like

Stenosis             Frequency

1                                 90 

2                                 27

3                                  8

4                                 31

5                                 38

Thanks in advance for your responses.

---

[yoyong]

Thank you very much for your help.

I have another question.

If I have STENOSIS1, STENOSIS2 and STENOSIS3, how do I get the total frequency for each category across the three variables?

[Reeza]

You need to restructure your data then so you have a single entry. A quick way is to use PROC TRANSPOSE to reformat the data so it has a single entry.

And if you have multiple values does the weighting/frequency structure change? Do you have different weights/frequency for each variable?

---

@yoyong wrote:
Thank you very much for your help.

I have another question.

If I have STENOSIS1, STENOSIS2 and STENOSIS3, how do I get the total frequency for each category across the three variables?

---

[Reeza]

Assuming you only have three more variables and the count is the same:

If count is not the same, create an array for those variables and assign it as well in the top most loop.

data have;
input @1 Stenosis $9.  @10  Frequency  ;
Stenosis2 = Stenosis;
Stenosis3 = Stenosis;
cards; 
1        70 
1,2      12 
1,3      8 
2,4,5    15
4,5      16
5        7
;;;;
run;

data expanded;
set have;

    array _sten(*) stenosis1-stenosis3;

    do j=1 to 3;
        stenosis=_sten(j);
        *count the number of words in the string;
        n_words = countw(stenosis);

        *create a record for each word;
        do i=1 to n_words;
            Word = scan(stenosis, i, ','); *extracts the word of interest;
            output; *outputs to data set;
        end;
    end;
    keep stenosis word frequency n_words; *keep only these variables in final data set;
run;

proc freq data=expanded;
table word;
weight frequency;
run;