topic Re: Creating a simple frequency table in SAS in SAS Procedures

Creating a simple frequency table in SAS

ginak — Tue, 08 Jan 2013 23:01:13 GMT

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

Re: Creating a simple frequency table in SAS

Reeza — Tue, 08 Jan 2013 23:04:56 GMT

What does your data look like?

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

Re: Creating a simple frequency table in SAS

ginak — Tue, 08 Jan 2013 23:16:39 GMT

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 😕

Re: Creating a simple frequency table in SAS

ginak — Tue, 08 Jan 2013 23:19:56 GMT

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.

Re: Creating a simple frequency table in SAS

Reeza — Tue, 08 Jan 2013 23:21:44 GMT

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

Re: Creating a simple frequency table in SAS

ginak — Tue, 08 Jan 2013 23:23:15 GMT

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

Re: Creating a simple frequency table in SAS

Reeza — Tue, 08 Jan 2013 23:32:08 GMT

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;

Re: Creating a simple frequency table in SAS

ginak — Tue, 08 Jan 2013 23:42:24 GMT

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 information I need to a new data set, and then merging those data sets. Thoughts?

Re: Creating a simple frequency table in SAS

art297 — Wed, 09 Jan 2013 00:12:02 GMT

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?

Re: Creating a simple frequency table in SAS

ginak — Wed, 09 Jan 2013 00:13:43 GMT

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

Re: Creating a simple frequency table in SAS

Reeza — Wed, 09 Jan 2013 01:28:51 GMT

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.

Re: Creating a simple frequency table in SAS

art297 — Wed, 09 Jan 2013 01:41:14 GMT

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;

run;

proc print data=want;

run;

Re: Creating a simple frequency table in SAS

Tom — Wed, 09 Jan 2013 04:34:38 GMT

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;