BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
geneshackman
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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;

View solution in original post

22 REPLIES 22
Reeza
Super User

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

geneshackman
Pyrite | Level 9

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.

Ksharp
Super User

Post your sample data and the output you want .

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

geneshackman
Pyrite | Level 9

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%

Reeza
Super User

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.

Ksharp
Super User

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;

Ksharp
Super User

Post your sample data and the output you want .

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

geneshackman
Pyrite | Level 9

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

Cynthia_sas
SAS Super FREQ

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;

geneshackman
Pyrite | Level 9

Thanks everyone. I'll try these suggestions.

geneshackman
Pyrite | Level 9

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

geneshackman
Pyrite | Level 9

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

Reeza
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 22 replies
  • 9819 views
  • 9 likes
  • 5 in conversation