I have survey data, asking organizations whether and how they will provide various services. For example, will they provide HIV testing, and will they provide it directly, by contract, by referral.
I'd like output showing the N of organizations who said yes and percent of organizations who said yes to each of those methods of providing the service. Something like this:
provide HIV testing
directly contract referral don't
N (%) N (%) N (%) N (%)
31 (50) 6 (10) 62 (100) 0 (0)
I have variables N and Percent but I want to put them together, two columns under one heading for each response type (directly, contract, referral, don't)
Is this possible, or do I have to ask SAS to calculate N and %?
Thanks
Hi:
Here's a simple example using PROC FREQ to get the COUNT and PERCENT values for each unique value for AGE and then 2 PROC REPORT steps that show how to make a character column with the 2 separate values concatenated.
Cynthia
** get count and percent in dataset;
proc freq data=sashelp.class;
tables age / out=work.frq nocum;
run;
ods html file='c:\temp\two_vars_one_cell.html';
proc report data=work.frq;
title '1) Concatenate variables from work.frq';
column age count percent newcell;
define age / order;
define count / sum;
define percent / sum;
define newcell / computed style(column)={just=r width=1in};
compute newcell / character length=25;
newcell = cat(count.sum,' (',put(percent.sum,6.2),'%)');
endcomp;
rbreak after / summarize;
run;
proc report data=work.frq;
title '2) Use NOPRINT on variables with summary';
column age count percent newcell;
define age / order;
define count / sum noprint;
define percent / sum noprint;
define newcell / computed style(column)={just=r width=1in} 'Count (%)';
compute newcell / character length=25;
newcell = cat(count.sum,' (',put(percent.sum,6.2),'%)');
endcomp;
rbreak after / summarize;
run;
ods html close;
I don't think it's possible directly in a single proc.
Generally, you'll have to do some pre-calculations and then use proc report to display your information.
You can see an older paper from Cynthia Zender - Creating Complex Reports for specific examples with code.
The paper can be found by searching at lexjansen.com
Thanks. It's here
http://www2.sas.com/proceedings/forum2008/173-2008.pdf
At first glance, looks useful. I'll read it in more detail tomorrow.
Post your sample data and the output you want .
I think proc sql + proc report ( across ) could obtain it .
Here is what some data looks like
Provide partner services
N provide % provide N provide % provide
directly directly by contract by contract
15 26% 13 22%
This is what your raw data looks like?
The code from RW9 is basic SAS functions nested in a long query. Given what you've posted your data may look like, I'm not sure it's applicable.
|| - concatenation
put - conversion to numeric
count - count occurences
strip - remove spaces
You can/should refer to the documentation for more details.
That would be easy. If you have already such layout .
data have;
input a1 a2 b1 b2;
cards;
15 .26 13 .22
;
run;
proc report data=have nowd split='*';
column a1 a2 a b1 b2 b;
define a1/display noprint;
define a2/display noprint;
define a/computed 'directly * N (%) ';
define b1/display noprint;
define b2/display noprint;
define b/computed 'contract * N (%) ';
compute a/character length=20 ;
a=catx(' ',a1,cats('(',a2*100,')'));
endcomp;
compute b/character length=20 ;
b=catx(' ',b1,cats('(',b2*100,')'));
endcomp;
run;
Post your sample data and the output you want .
I think proc sql + proc report ( across ) could obtain it .
Hi,
Yes, I tend to use proc sql to generate these types of tables:
proc sql;
create table WANT
(
DIRECTLY char(200) label="directly|N (%)", /* Pipe is splt char */
CONTRACT char(200) label=...,
REFERRAL char(200) label=...,
DONT char(200) label=...
);
select count(DIRECTLY_BIGN)
into :DIRECTLY_N
from TABLE
where ...;
/* Repeat above for each bign */
insert into WANT
set DIRECTLY=(select strip(put(count(DIRECTLY_VAR),best.)||" ("||strip(put((DIRECTLY_VAR / &DIRECTLY_N.) * 100,5.1))||")" from HAVE),
CONTRACT=...;
quit;
If you already have the data then you can substitue the subclause with select that data.
RW9: is this part
"set DIRECTLY=(select strip(put(count(DIRECTLY_VAR),best.)||" ("||strip(put((DIRECTLY_VAR / &DIRECTLY_N.) * 100,5.1))||")" from HAVE),"
the part that puts two columns under one heading? Is there a document or website on the web I can look at to understand what this is doing?
Thanks
Hi:
Here's a simple example using PROC FREQ to get the COUNT and PERCENT values for each unique value for AGE and then 2 PROC REPORT steps that show how to make a character column with the 2 separate values concatenated.
Cynthia
** get count and percent in dataset;
proc freq data=sashelp.class;
tables age / out=work.frq nocum;
run;
ods html file='c:\temp\two_vars_one_cell.html';
proc report data=work.frq;
title '1) Concatenate variables from work.frq';
column age count percent newcell;
define age / order;
define count / sum;
define percent / sum;
define newcell / computed style(column)={just=r width=1in};
compute newcell / character length=25;
newcell = cat(count.sum,' (',put(percent.sum,6.2),'%)');
endcomp;
rbreak after / summarize;
run;
proc report data=work.frq;
title '2) Use NOPRINT on variables with summary';
column age count percent newcell;
define age / order;
define count / sum noprint;
define percent / sum noprint;
define newcell / computed style(column)={just=r width=1in} 'Count (%)';
compute newcell / character length=25;
newcell = cat(count.sum,' (',put(percent.sum,6.2),'%)');
endcomp;
rbreak after / summarize;
run;
ods html close;
Thanks everyone. I'll try these suggestions.
Reeza asked for original data. Looks like this:
place focus
group review interview survey mapping
place1 yes no no no no
place2 yes no no yes no
place3 yes yes yes yes yes
place4 yes yes no yes yes
place5 yes no yes no no
place6 yes yes yes no yes
place7 yes yes yes no no
place8 yes no no no yes
this is some of it
Thanks folks again.
Cynthia, I want to ask, are those two separate ways to do the proc report, or a two step process where both are needed?
Cynthia and Xia, just to clarify, the N in these tables is the number of people who said yes, they do one of the things, not the number of people who responded to the question. Do I need to do any additional computation to get that N?
Also, the data set I am working with now is the sums, so the data set has sum of people saying they do one of those things, the percent of people doing those things. Would I need to go back to the raw data set and have proc report calculate the sums and percents, or okay to use the data set that already has those?
If your response already says about those questions, sorry I don't understand.
Thanks
If you post a sample of your raw data, it would be easier to answer your question.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.