Solved
Contributor
Posts: 54

# proc report, two variables in one combined column

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
SAS Super FREQ
Posts: 9,038

## Re: proc report, two variables in one combined column

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
Super User
Posts: 21,546

## Re: proc report, two variables in one combined column

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

Contributor
Posts: 54

## Re: proc report, two variables in one combined column

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.

Super User
Posts: 10,320

## Re: proc report, two variables in one combined column

Post your sample data and the output you want .

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

Contributor
Posts: 54

## Re: proc report, two variables in one combined column

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%

Super User
Posts: 21,546

## Re: proc report, two variables in one combined column

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.

Super User
Posts: 10,320

## Re: proc report, two variables in one combined column

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;

Super User
Posts: 10,320

## Re: proc report, two variables in one combined column

Post your sample data and the output you want .

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

Super User
Posts: 8,634

## Re: proc report, two variables in one combined column

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.

Contributor
Posts: 54

## Re: proc report, two variables in one combined column

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
SAS Super FREQ
Posts: 9,038

## Re: proc report, two variables in one combined column

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;

Contributor
Posts: 54

## Re: proc report, two variables in one combined column

Thanks everyone. I'll try these suggestions.

Contributor
Posts: 54

## Re: proc report, two variables in one combined column

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

Contributor
Posts: 54

## Re: proc report, two variables in one combined column

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?

Thanks

Super User
Posts: 21,546