Creating a simple frequency table in SAS

[ginak]

Hello! I feel that this is something super simple and it's just not hitting me right now. I would like to create a frequency table. I have 10 variables, each one corresponds to a different treatment. I just want to see how many people, out of the 8,000 people in my data set, used each treatment. You could have used more than 1 treatment. For example, person one may have used treatments 2 and 6. I would like to know the number of people that used each treatment. So the table I want would look like:

treatment	n	%
trtmnt1
trtmnt2
trtmnt3
trtmnt4
trtmnt5
trtmnt6
trtmnt7
trtmnt8
trtmnt9
trtmnt10
total

Where % is just looking at the % in each treatment (i.e., all the entries in the % column should add up to 100). for the row "total," n could be more than 8,000 because you could choose more than 1 treatment (this is assuming that there are no missing values, which there are in my data set. but for explanation purposes i'm pretending that there are no missing values).

I was thinking of using proc report, but I'm not sure how to go about this..

proc report data= followup;

     column n pctsum;

     ????

run;

I'm just not sure how to input the 10 different types of treatments.

In another data step, I tried making a new variable called 'treatment' where: "if trtmnt1=1 then treatment =1;

if trtmnt2=1 then treatment=2;...... etc"  all the way til trtmnt10.

But then I noticed that my frequencies were changing! Suppose I had 1400 people who marked 1 for trtmnt1, I did a proc freq on treatment and saw that there were only 100 something people who marked 1 for treatment. How would I be able to just take the tallies of each treatment and put that in a table? Would that be easier than using proc report?Thanks a bunch..

I'm kind of new to SAS so I'm not experienced and have been googling all day

[Reeza]

What does your data look like?

Otherwise you could just run a proc freq or proc tabulate.

[ginak]

Hi,

I wanted to do a proc freq. I did run a proc freq as so:

proc freq data = followup;

tables trtmnt1 trtmnt2 trtmnt3 trtmnt4 trtmnt5 trtmnt6 trtmnt7  trtmnt8 trtmnt9 trtmnt10;

run;

but this gives me

                                       The FREQ Procedure

                                 trtmnt1

                                                        Cumulative    Cumulative

                   trtmnt1    Frequency     Percent     Frequency      Percent

                          1         153      100.00           153       100.00

                                    Frequency Missing = 8727

.

.

.

                               trtmnt10

                                                        Cumulative    Cumulative

                   trtmnt10    Frequency     Percent     Frequency      Percent

                          1         771      100.00           771       100.00

                                    Frequency Missing = 8109

So I'm not sure how to get a table that looks like the one I pasted my original post 😕

[Reeza]

So basically your data is 1 or missing for each treatment?

[ginak]

Yep, exactly! They either have a 1 or it's just missing for each treatment

[Reeza]

If you have 1 and 0's rather than 1 or missing its easier to deal with.

It still is a multistep process.

1. count all the 1's.

2. (transpose depending on version of proc means) If you have 9.3 look at the STACKODS option in proc means output.

3. Calculate percentages

data have;

    array treatment(10) treatment1-treatment10;

    do person=1 to 100;

        do i=1 to 10;

            if RAND('BERNOULLI',0.5)<=0.5 then treatment(i)=1; else treatment(i)=.;

        end;

    output;

    end;

run;

proc means data=have sum mean;

var treatment1-treatment10;

output out=summary1 sum=;

run;

[ginak]

Thank you! Unfortunately I have 30 treatments and each with different names I just simplified it in here for explanation purposes. So the array wouldn't work then, right? I'm using SAS 9.2.

I found a website which has a macro that creates tables; the only problem is that I am not cross-tabulating by anything. I simply want variables in one column,  frequencies in the next and % in the last column.. so I'm not sure how to do this

http://www.nesug.org/proceedings/nesug04/ap/ap15.pdf

Update:::: Someone suggested outputting the info...

[Reeza]

The array is only used to create the sample data since you didn't provide any. If your variables are side by side you can reference them in proc means using

double dashes.

first_variable--second_variable

You can transpose the dataset from proc means and then calculate your totals in a separate step and remerge it in. 

[art297]

Does the following do what you want?:

data treatments;

  input sub treatment1-treatment10;

  cards;

1 . . . 1 . . 1 . . .

2 1 . . . . . . . . .

3 . 1 . 1 . . 1 . . .

;

ods listing close;

ods output OneWayFreqs=OneWay;;

proc freq data=treatments;

  tables treatment:;

run;

ods listing;

data want (keep=table frequency percent);

  do until (eof);

    set oneway (keep=table frequency) end=eof ;

    total+frequency;

  end;

  do until (eof2);

    set oneway (keep=table frequency) end=eof2;

    percent=100*frequency/total;

    table=tranwrd(table,"Table ","");

    output;

    if eof2 then do;

      Table="Total";

      frequency=total;

      percent=100.0;

      output;

    end;

  end;

run;

proc print data=want;

run;

[art297]

You said that you want the total % to add up to 100%.  Thus, do you want it based on total number of responses regardless of whether some of them represent the same individuals?

[ginak]

That is correct; it's fine if there is overlap

[ginak]

Was that helpful?The follow-up data is pretty complicated. I'm just looking at their 3 month follow-up because it was in long format. So I used a data step to isolate just to the 3-month follow up visit and will look at their treatments at 3-month follow-up. There are 10 treatments (each treatment is its own binary variable) and patients may check all that apply.

[Tom · Accepted solution]

You can use PROC SUMMARY to generate the counts of the number of subjects with 1 for each treatment by requesting the SUM statistic.  You can then divide by the _FREQ_ to get the percentage.

* Create some example data ;

data treatments;

  input sub treatment1-treatment10;

  cards;

1 . . . 1 . . 1 . . .

2 1 . . . . . . . . .

3 . 1 . 1 . . 1 . . .

;

%* Set the list of treatment variables ;

%let varlist=treatment1-treatment10;

proc means data=treatments;

  var &varlist ;

  output out=summary(drop=_type_) sum=;

run;

proc transpose data=summary out=want(rename=(_name_=treatment col1=n)) ;

  by _freq_ ;

run;

data want;

  set want;

  n=sum(n,0);

  pct=100*n/_freq_;

run;