topic Re: Adding Frequencies in SAS Programming

Adding Frequencies

yoyong — Mon, 16 Jul 2018 21:07:33 GMT

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.

Re: Adding Frequencies

novinosrin — Mon, 16 Jul 2018 21:40:53 GMT

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;

Re: Adding Frequencies

novinosrin — Mon, 16 Jul 2018 21:42:31 GMT

@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

Re: Adding Frequencies

Reeza — Mon, 16 Jul 2018 21:44:10 GMT

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.

Re: Adding Frequencies

yoyong — Mon, 16 Jul 2018 22:26:07 GMT

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?

Re: Adding Frequencies

Reeza — Mon, 16 Jul 2018 22:45:17 GMT

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?

Re: Adding Frequencies

Reeza — Mon, 16 Jul 2018 22:50:44 GMT

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;