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
- /
- ODS and Base Reporting
- /
- proc report, two variables in one combined column

- Subscribe to 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
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-12-2015 01:17 PM

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

Accepted Solutions

Solution

03-14-2015
08:05 PM

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

Posted in reply to geneshackman

03-14-2015 08:05 PM

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;**

All Replies

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

Posted in reply to geneshackman

03-12-2015 03:03 PM

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

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

Posted in reply to Reeza

03-12-2015 04:02 PM

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.

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

Posted in reply to geneshackman

03-13-2015 05:01 AM

Post your sample data and the output you want .

I think proc sql + proc report ( across ) could obtain it .

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

Posted in reply to Ksharp

03-13-2015 03:37 PM

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%

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

Posted in reply to geneshackman

03-13-2015 05:07 PM

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.

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

Posted in reply to geneshackman

03-14-2015 02:19 AM

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;

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

Posted in reply to geneshackman

03-13-2015 05:03 AM

Post your sample data and the output you want .

I think proc sql + proc report ( across ) could obtain it .

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

Posted in reply to geneshackman

03-13-2015 05:30 AM

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 IRECTLY_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.

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

03-13-2015 03:41 PM

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

Solution

03-14-2015
08:05 PM

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

Posted in reply to geneshackman

03-14-2015 08:05 PM

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;**

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

Posted in reply to Cynthia_sas

03-16-2015 08:52 AM

Thanks everyone. I'll try these suggestions.

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

Posted in reply to geneshackman

03-16-2015 11:30 AM

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

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

Posted in reply to Cynthia_sas

03-16-2015 09:46 AM

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

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

Posted in reply to geneshackman

03-16-2015 11:00 AM

If you post a sample of your raw data, it would be easier to answer your question.