BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hi all,
I am rather new to SAS (switched from spss).

Is there a tabulate or report command that will provide count and percentages in rows for multiple variables on the same scale?

Example:
......... ______________ agree _________neutral________ disagree
Var1...______________ 5 (25%)_______10 (50%)________ 5 (25%)
Var2...______________ 10 (50%)______ 5 (25%) ________ 5 (25%)
Var3, etc...

(the lines are in the example so the columns are aligned a little).

Or something similar, the N and the % could be on separate lines.
The important part is to be able to present multiple VARIABLES within one table when they are on the same scale.

Thanks,
K
10 REPLIES 10
Cynthia_sas
SAS Super FREQ
Hi:
This seems like a job for PROC TABULATE, although, you would get the N and the PCTN in different columns. For example, SASHELP.CLASS is one of the sample datasets provided when you install SAS. It is a file of 19 students who range in age from 11-16. If you wanted to find the N and PCTN for each age (AGE) broken down by gender (SEX), you would use the following code:
[pre]
proc tabulate data=sashelp.class;
class age sex ;
table age all,
sex*(n*f=comma6. pctn) all*(n*f=comma6. pctn);
run;
[/pre]

which would result in this output:
[pre]
+--------+---------------------------------------+-------------------+
| | Sex | |
| |-------------------+-------------------+ |
| | F | M | All |
| |------+-------------------+-------------------+------------+
| | N | PctN | N | PctN | N | PctN |
|--------------------------------------------------------------------+
|Age | | | | | | |
|--------+ | | | | | |
|11 | 1| 5.26| 1| 5.26| 2| 10.53|
|--------------------------------------------------------------------+
|12 | 2| 10.53| 3| 15.79| 5| 26.32|
|--------------------------------------------------------------------+
|13 | 2| 10.53| 1| 5.26| 3| 15.79|
|--------------------------------------------------------------------+
|14 | 2| 10.53| 2| 10.53| 4| 21.05|
|--------------------------------------------------------------------+
|15 | 2| 10.53| 2| 10.53| 4| 21.05|
|--------------------------------------------------------------------+
|16 | .| .| 1| 5.26| 1| 5.26|
|--------------------------------------------------------------------+
|All | 9| 47.37| 10| 52.63| 19| 100.00|
+--------+------+------------+------+------------+------+------------+
[/pre]

By default PCTN gives you a percent of the GRAND TOTAL (19), but you can also ask for ROWPCTN (percent of ROW TOTAL) or COLPCTN (percent of COL TOTAL).

Here are some papers that introduce using PROC TABULATE:
http://www2.sas.com/proceedings/sugi30/258-30.pdf
http://www.scsug.org/SCSUGProceedings/2007/papers/how/HOW-Winn.pdf
http://www2.sas.com/proceedings/sugi30/127-30.pdf
http://www2.sas.com/proceedings/sugi25/25/iv/25p159.pdf

cynthia
deleted_user
Not applicable
I looked at Tabulate in that way, but that is not what I need.
Let me try and describe again the way it should look.
Example:
The data has three variables (var1, var2, and var3) that all have responses on a 1 to 5 scale (1=very poor, 2=poor, 3= adequate, 4=good, and 5=very good).
The table to produce would be a one-way table summarizing the three variables on the same scale.

var-lable---------very poor------poor--------adequate--------good----------very good----noresponse
Var1 (n)--------18-----------------26--------------95---------------163----------------87--------------15---------
Var1 (%)--------4.5%--------6.4%----------23.5%----------40.3%-----------21.5%----------3.7%----

Var2 (n)------------17-------------32--------------90---------------146----------------102------------17---------
Var2 (%)-----------4.2%--------7.9%----------22.3%----------36.1%-----------25.2%----------4.2%----

The two-way tables are fairly easy. It is the oneway tables with multiple variables that I am having a bit of trouble with at this point. With the other software, it was fairly easy. And it is this one layout that is slowing me down.
Peter_C
Rhodochrosite | Level 12
OK
since you didn't offer some sample data for testing, time has been wasted when you might have had an earlier answer, as I create some.
First a format to make 1-5 meaningful responses[pre]proc format ;
value satisfy
1='1 very poor' 2='2 poor' 3='3 adequate' 4='4 good'
5='5 very good' other = '99 no response' ;
run ;[/pre] next creating some data I hope is in the structure you seem to indicate
namely var1-var3 holding integers in range 1-5 or no response .[pre]data test15 ;
format var1-var3 satisfy. ;
do test=1 to 100 ;
var1 = abs(ranNor( 2 ) ); * some kind of positive number ;
var2 = abs(ranNor( 3 ) );
var3 = abs(ranNor( 7 )) ;
var1 = int(mod( var1 * 113, 5 )) ; * some integer in range 0-5 ;
var2 = int(mod( var2 * 113, 5 )) ;
var3 = int(mod( var3 * 113, 5 )) ;
output ;
end ;
run;[/pre] now generate a view for tabulate processing - reorganised with one response value column and a "class" variable named "varN" which holds the original variable name or perhaps cound hold the questionaire question number.[pre]data testV / view= testV;
set test15 ;
varN = 'var1' ; resp = var1 ; output ;
varN = 'var2' ; resp = var2 ; output ;
varN = 'var3' ; resp = var3 ; output ;
keep varN resp test ;
format resp satisfy. ;
run ;[/pre]and now to tabulate these responses[pre] proc tabulate order=formatted ;
class varN resp ;
table ( varN all )*( n *f=7. pctn='%'*f= 8.1 ), resp / rts=30 ;
run ;[/pre]which generated this table for me (lines removed)[pre] resp

1 very 3 99 no
poor 2 poor adequate 4 good response

varN

var1 N 13 23 29 16 19

% 4.3 7.7 9.7 5.3 6.3

var2 N 16 32 20 15 17

% 5.3 10.7 6.7 5.0 5.7

var3 N 18 18 18 21 25

% 6.0 6.0 6.0 7.0 8.3

All N 47 73 67 52 61

% 15.7 24.3 22.3 17.3 20.3
[/pre]Surely there is a user format already demonstrated on the Forums, which would provide the trailing % synmbol without the 100 multiplier in the standard percent. format?
I'll leave karen to search for that.

regards
peterC
Cynthia_sas
SAS Super FREQ
It's a PICTURE format -- example here:
http://support.sas.com/kb/38/001.html

cynthia
Peter_C
Rhodochrosite | Level 12
(yet again) many thanks Cynthia
picture mypct low-high='000,009%';
lifted from that Sample in the Knowledge Base
adapted to provide a decimal place -->
picture mypc_t low-high='0,009.1%';
dropped into the proc format of my earlier demo
enhances the results by updating the table statement to
(also removing redundant lines - class name header and resp= header
easy to spot here, but not always possible )
table ( varN=' ' all )*( n *f=7. pctn='%'*f= mypc_t8. ), resp=' '
/ box="response analysis" rts=30 ;
produces this table[pre] response 1 very 3 99 no
analysis poor 2 poor adequate 4 good response

var1 N 13 23 29 16 19

% 4.3% 7.6% 9.6% 5.3% 6.3%

var2 N 16 32 20 15 17

% 5.3% 10.6% 6.6% 5.0% 5.6%

var3 N 18 18 18 21 25

% 6.0% 6.0% 6.0% 7.0% 8.3%

All N 47 73 67 52 61

% 15.6% 24.3% 22.3% 17.3% 20.3%[/pre] OK? might be wise to use ROWPCTn instead of PCTn or the percentage denominator is the sum over all class values of "varN2" but prefered is thateach row be on it's own

Message was edited by: Peter.C
deleted_user
Not applicable
Thank you all!
This does get me to where I want to go!

Now, to duplicate en mass. 🙂

Karen
Peter_C
Rhodochrosite | Level 12
Karen



> This does get me to where I want to go!
>
> Now, to duplicate en mass. 🙂
>

"duplicate en mass" sounds like something for another forum "SAS Macro Facility, Data Step and SAS Language Elements" at http://support.sas.com/forums/forum.jspa?forumID=31
deleted_user
Not applicable
well, I am not to that point ...yet
Peter_C
Rhodochrosite | Level 12
Karen

in general, when you had 0/1 variables for responses, the mean would provide the "percentage" of the "1" cases which I am assuming is what you want.
You don't need to rebuild your data collection to change Y/N responses into 1/0. Just use a view like:[pre] data class_view / view= class_view ;
set sashelp.class ;
girls = ( sex='F' ) ;
teenager = ( age > 12 ) ;
run ; [/pre]Notice that sum( girls ) will provide the number of girls and mean( girls)*100 would provide a percentage. There is a SAS format to do that multiplication for us.
To obtain the percentage of teenagers and girls, among that "population", needs tabulate statements like[pre]proc tabulate data= class_view ;
var girls teenager ;
table ( girls teenager ) *mean='%' *f=percent8.
all='total' *n *f= 6.
, all='stats'/rts=30 ;
run ;[/pre]oddly that provides a column with two % stats and a count. We don't often see Tabulate do different formats in the same column 😉
We get that in this demo because I defined different formats before the comma ','
That area of the statement defines what appears in the "row title space". Unfortunately, it is often not possible to do this with tabulate because of what we want to do with the column definitions (which follow that ',' ).

peterC
. [likes] tabulate
results (having taken out the formchar )[pre] stats

girls % 47%

teenager % 63%

total N 19[/pre]
data_null__
Jade | Level 19
SAS just doesn't have that N(%) style display that is used in every table I need too. There are many ways to do it. I like to fiddle with the output from PROC FREQ and then use PROC REPORT. I think this look pretty good and I believe is the format you you said you want.


[pre]
data sim; * perhaps this is similar to your data;
array v
  • var10-var1 var11-var30 var58-var31;
    do _n_ = 1 to 100;
    do j = 1 to dim(v);
    v = rantbl(12345,.06,.16,.3,.3,.16);
    end;
    output;
    end;
    drop j;
    run;
    ods listing close;
    ods output OneWayFreqs=OneWayFreqs;
    proc freq;
    run;
    ods listing;
    data display;
    length row0 8 row $32 level0 8 level 8 cell $16;
    set OneWayFreqs;
    by table notsorted;
    if first.table then do;
    row0 + 1;
    level0 = 0;
    end;
    level0 + 1;
    row = scan(table,-1,' ');
    level = coalesce(of var:);
    cell = catx(' ',frequency,cats('(',vvalue(percent),'%)'));
    format percent 10.1;
    keep row: level: cell;
    run;
    proc print;
    run;
    ods rtf file='test.rtf';
    ods listing close;
    proc format;
    value vresp 1='very poor' 2='poor' 3='adequate' 4='good' 5='very good' 6='noresponse';
    run;
    proc report nowd list;
    columns row0 row cell, level dummy;
    define row0 / group noprint;
    define row / group width=16 'Variable';
    define level / across order=internal format=vresp. 'Response';
    define cell / display width=10 center ' ';
    define dummy / noprint; *necessary evil;
    run;
    quit;
    ods rtf close;
    ods listing;
    [/pre]
  • 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
    • 10 replies
    • 1686 views
    • 0 likes
    • 4 in conversation