turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Creating a simple frequency table in SAS

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-08-2013 06:01 PM

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

Accepted Solutions

Solution

01-08-2013
11:34 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ginak

01-08-2013 11:34 PM

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;

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ginak

01-08-2013 06:04 PM

What does your data look like?

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

01-08-2013 06:16 PM

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 :/

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ginak

01-08-2013 06:21 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

01-08-2013 06:23 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ginak

01-08-2013 06:32 PM

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;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

01-08-2013 06:42 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ginak

01-08-2013 08:28 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ginak

01-08-2013 08:41 PM

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;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

01-08-2013 07:12 PM

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?

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to art297

01-08-2013 07:13 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

01-08-2013 06:19 PM

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.

Solution

01-08-2013
11:34 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ginak

01-08-2013 11:34 PM

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;