proc report, two variables in one combined column

[geneshackman]

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?

[Reeza]

Before summary is probably better in this case.

[Ksharp]

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,

        calculated N_yes/(calculated N_yes+calculated N_no) as Per_yes format=percent8.2,1-calculated Per_yes as Per_no format=percent8.2

from have

union all

select 'review' as Activities length=20,sum(review='yes') as N_yes,sum(review='no') as N_no,

        calculated N_yes/(calculated N_yes+calculated N_no) as Per_yes format=percent8.2,1-calculated Per_yes as Per_no format=percent8.2

from have

union all

select 'interview' as Activities length=20,sum(interview='yes') as N_yes,sum(interview='no') as N_no,

        calculated N_yes/(calculated N_yes+calculated N_no) as Per_yes format=percent8.2,1-calculated Per_yes as Per_no format=percent8.2

from have

union all

select 'survey' as Activities length=20,sum(survey='yes') as N_yes,sum(survey='no') as N_no,

        calculated N_yes/(calculated N_yes+calculated N_no) as Per_yes format=percent8.2,1-calculated Per_yes as Per_no format=percent8.2

from have

union all

select 'mapping' as Activities length=20,sum(mapping='yes') as N_yes,sum(mapping='no') as N_no,

        calculated N_yes/(calculated N_yes+calculated N_no) as Per_yes format=percent8.2,1-calculated Per_yes as Per_no format=percent8.2

from have

;

quit;

[RW9]

Just to add, you can save yourself some typing by generating the code:

proc sql;

     create table WANT

     ( activities char(20), n_yes char(20),...);

quit;

data _null_;

     array tests{5} $200. ("focus_group","review","interview","survey","mapping");

     do i=1 to dim(tests);

          call execute('proc sql;

                                   insert into WANT

                                   set     ACTIVITIES="'||strip(tests{i}||'",

                                            N_YES=(select sum('||strip(tests{i})||') from have),

                                             ...;

                              quit;');

     end;

run;

A bit like a macro where you generate the code from parameters, metadata programming.

[geneshackman]

Xia

Thanks for the suggestion.. I tried the last one, proc sql and still got columns for each possible response. Thanks though.

Gene

      

activities	N yes	N No	Per Yes	Per No
focus group	8	0	100.0%	0.00%
review	4	4	50.00%	50.00%
interview	4	4	50.00%	50.00%
survey	3	5	37.50%	62.50%
mapping	4	4	50.00%	50.00%

[geneshackman]

Okay, a colleague suggested what a couple of folks mentioned, concatenation, like this

Newvar=put(N_pd,3.)||" ("||put(pct_pd,3.)||")";

I worked with that and got it.  Others mentioned this, but there was so much other stuff along with it. It is difficult sometimes when not working face to face.

Thanks again.

[Cynthia_sas]

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

[geneshackman]

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;