topic Re: proc report, two variables in one combined column in ODS and Base Reporting

proc report, two variables in one combined column

geneshackman — Thu, 12 Mar 2015 17:17:20 GMT

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

Re: proc report, two variables in one combined column

Reeza — Thu, 12 Mar 2015 19:03:11 GMT

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

Re: proc report, two variables in one combined column

geneshackman — Thu, 12 Mar 2015 20:02:20 GMT

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.

Re: proc report, two variables in one combined column

Ksharp — Fri, 13 Mar 2015 09:01:44 GMT

Post your sample data and the output you want .

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

Re: proc report, two variables in one combined column

Ksharp — Fri, 13 Mar 2015 09:03:08 GMT

Post your sample data and the output you want .

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

Re: proc report, two variables in one combined column

RW9 — Fri, 13 Mar 2015 09:30:35 GMT

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.

Re: proc report, two variables in one combined column

geneshackman — Fri, 13 Mar 2015 19:37:53 GMT

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%

Re: proc report, two variables in one combined column

geneshackman — Fri, 13 Mar 2015 19:41:24 GMT

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

Re: proc report, two variables in one combined column

Reeza — Fri, 13 Mar 2015 21:07:13 GMT

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.

Re: proc report, two variables in one combined column

Ksharp — Sat, 14 Mar 2015 06:19:14 GMT

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;

Re: proc report, two variables in one combined column

Cynthia_sas — Sun, 15 Mar 2015 00:05:27 GMT

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;

Re: proc report, two variables in one combined column

geneshackman — Mon, 16 Mar 2015 12:52:57 GMT

Thanks everyone. I'll try these suggestions.

Re: proc report, two variables in one combined column

geneshackman — Mon, 16 Mar 2015 13:46:25 GMT

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

Re: proc report, two variables in one combined column

Reeza — Mon, 16 Mar 2015 15:00:08 GMT

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

Re: proc report, two variables in one combined column

geneshackman — Mon, 16 Mar 2015 15:08:26 GMT

I'll try. Not sure how well this will show up.

Which activities do you do?

Activities N.yes N.no %.yes %.no

focus groups 39 0 100% 0%

interviews 18 21 46.2% 53.8%

surveys 19 20 48.7% 51.3%

mapping 16 23 41% 59%

That make sense? Or would you need the raw data, before I did any sums and percents?

Re: proc report, two variables in one combined column

Reeza — Mon, 16 Mar 2015 15:12:21 GMT

Before summary is probably better in this case.

Re: proc report, two variables in one combined column

geneshackman — Mon, 16 Mar 2015 15:30:54 GMT

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

Re: proc report, two variables in one combined column

Cynthia_sas — Mon, 16 Mar 2015 17:19:34 GMT

HI:

If you run both PROC REPORTS, you will see that the first REPORT does not have NOPRINT and the second REPORT does have NOPRINT. They are the SAME report otherwise. The one you would use would depend on your preference. Since I did not have any data with YES/NO, I did not do anything about the %of YES vs the %of NO. I used straight counts and percents as calculated by PROC FREQ. If you have particular needs or you have pre-summarized data, then you might need a different approach.

cynthia

Re: proc report, two variables in one combined column

geneshackman — Mon, 16 Mar 2015 19:12:36 GMT

Okay, I sort of have some of it. See this file

http://www.pharmasug.org/proceedings/2012/TF/PharmaSUG-2012-TF20-SAS.pdf

using this code (I modified it a little) around page 13 (don't need the "call" line")

data test;

input type $ color $ counter;

cards;

focus Nyes 1

focus Pctyes 2

survey Nno 1

survey Pctno 2

;

run;

proc report nowd data=test missing ;

col counter type,color,counter=num;

*define counter / group ' ';

define type / across ' ';

define color / across ' ';

define num / sum ' ' nozero;

compute num;

*call define(4,'style','style=[background=purple]');

endcomp;

run;

Re: proc report, two variables in one combined column

Ksharp — Tue, 17 Mar 2015 07:09:31 GMT

data have;

input (place focus_group review interview survey mapping) ($);

cards;

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

;

run ;

proc sql;

select 'focus group ' as Activities length=20,sum(focus_group='yes') as N_yes,sum(focus_group='no') as N_no,