turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- PCT in PROC TABULATE

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

dear all,

this is an fictive example . . . .

I want a total (ALL) per region (actual+predict) and the spread IN percent in the year, but my code below fails . . . can someone help me out please?

%LET dev=CANADA;

**proc** **tabulate** data=sashelp.prdsale missing;

where country="&dev.";

by region ;

class region division year;

var actual predict ;

table region*(actual predict all ) , year *(sum=''*f=commax7. colpctsum='%'*f=**6.2**)

/rts=**60** box="&dev." ;

**RUN**;

OUT SHOULD BE:

Accepted Solutions

Solution

3 weeks ago

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

Hi, your formula won't work for PROC REPORT. You are ALMOST there. But you need to use absolute column numbers when your computed item is under an ACROSS variable. Refer to this paper, http://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf on page 9 for an example of using absolute column numbers for custom calculations.

cynthia

cynthia

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

as an extra to my first questions the real output should be like this (i have no data yet):

if you give me sas syntax you may call the variabels A, B, C, . . . . like the column names

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

@Jaheuk I've merged these posts together as they're related.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

Hi:

When you use COLPCTSUM, you are asking explicitly for the percent of the column total. So the 50.67 is the result of (64.130 * 100) / 126.556 -- which is how TABULATE calculates COLPCTSUM. Have you tried other percent statistics, such as ROWPCTSUM or PCTSUM?

I am not sure what statistic mean when you ask for "spread IN percent", as TABULATE does not typically have the same kind of ability to do custom formulas, such as REPORT.

I do not understand the second screen shot you posted and without data, it's hard to see what you want.

My first suggestion would be to try other percent statistics. My second suggestion would be to annotate the SASHELP.PRDSALE example above in order to example exactly what you want to see other than what TABULATE is calculating for you.

cynthia

When you use COLPCTSUM, you are asking explicitly for the percent of the column total. So the 50.67 is the result of (64.130 * 100) / 126.556 -- which is how TABULATE calculates COLPCTSUM. Have you tried other percent statistics, such as ROWPCTSUM or PCTSUM?

I am not sure what statistic mean when you ask for "spread IN percent", as TABULATE does not typically have the same kind of ability to do custom formulas, such as REPORT.

I do not understand the second screen shot you posted and without data, it's hard to see what you want.

My first suggestion would be to try other percent statistics. My second suggestion would be to annotate the SASHELP.PRDSALE example above in order to example exactly what you want to see other than what TABULATE is calculating for you.

cynthia

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

Jaheuk wrote:

dear all,

this is an fictive example . . . .

I want a total (ALL) per region (actual+predict) and the spread IN percent in the year, but my code below fails . . . can someone help me out please?

"Code fails" is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of a dataset, the actual results and the expected results. Data should be in the form of a data step. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

I ran your code an get errors. You are requesting improper crossings of statistics that Tabulate can not do.

ERROR: Statistic other than N was requested without analysis variable in the following nesting : REGION * All * YEAR * Sum * f. ERROR: Statistic other than N was requested without analysis variable in the following nesting : REGION * All * YEAR * ColPctSum * f.

SUM and PCTSUM can only be applied to a variable defined as an analysis statement using a VAR statement. ALL can only be used in ther role of a class variable not a "sum".

In this case, for the procedure you have chosed and the output the data is in the wrong structure. Any time you want to have an "All" to get a total in Tabulate it really only works as intended when the groups are designated by a single Class variable.

data need; set sashelp.prdsale; length Incgrp $ 10; IncGrp = 'Actual';Sales=Actual;output; IncGrp = 'Predicted';Sales=predict;output; drop actual predict; run; %LET dev=CANADA; proc tabulate data=need missing; where country="&dev."; by region ; class region division year IncGrp; var Sales ; table region *(IncGrp='' All='Total' )*Sales , year * (sum=''*f=commax7. colpctsum='%'*f=6.2) /rts=60 box="&dev." ; RUN;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

**dear all,**

**sorry for all confusions,**

**below table like it should be and **

**code to create the datasets i****n two ways: numeric in one or multiple columns.**

**green cells are related and orange cells are related.**

**I tried to use either PROC TABULATE or REPORT but without good result.**

**Below the PROC REPORT I was strungeling with:**

**proc** **report** data=vert missing nowd out=REPORT;

column vkpnt groep mnd,(aantal perct) ;

define vkpnt / group ;

define groep / group order=data ;

define mnd / across ' ';

define aantal / SUM '#';

define perct / COMPUTED 'PCT';

compute perct;

perct = perct / aantal.sum ;

endcomp;

**run**;

** **

**/***********************************************************************/**

**data** VERT;

mnd='1708';

div='ANTW/LIM';

mang='MARC';

aorg='1006';

vkpnt='LESSELIERS';

groep='totaal';

aantal=**100**;

OUTPUT;

groep='TOTNOK';

aantal=**50**;

OUTPUT;

groep='eid';

aantal=**10**;

OUTPUT;

groep='fisc';

aantal=**20**;

OUTPUT;

groep='tele';

aantal=**20**;

OUTPUT;

mnd='1709';

div='ANTW/LIM';

mang='MARC';

aorg='1006';

vkpnt='LESSELIERS';

groep='totaal';

aantal=**105**;

OUTPUT;

groep='TOTNOK';

aantal=**45**;

OUTPUT;

groep='eid';

aantal=**5**;

OUTPUT;

groep='fisc';

aantal=**20**;

OUTPUT;

groep='tele';

aantal=**15**;

OUTPUT;

groep='fatca';

aantal=**5**;

OUTPUT;

**RUN**;

**data** HORZ;

mnd='1708';

div='ANTW/LIM';

mang='MARC';

aorg='1006';

vkpnt='LESSELIERS';

totaal=**100**;

tot_mis=**50**;

eid=**10**;

fisc=**20**;

tele=**20**;

OUTPUT;

mnd='1709';

div='ANTW/LIM';

mang='MARC';

aorg='1006';

vkpnt='LESSELIERS';

totaal=**105**;

tot_mis=**45**;

eid=**5**;

fisc=**20**;

fatca=**5**;

tele=**15**;

OUTPUT;

**RUN**;

REGARDS,

Herman

Solution

3 weeks ago

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

cynthia

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

I'm not going to quote your code but all of the SAS report procedures, Report, Tabulate or Print use a single data set for input. You show two separate data sets. So it appears that you may need to combine those two since I believe that you have elements from both sets in your wanted table picture. Also your desired percentages for TOTNOK row is not clear exactly where the denominator would come from.

If mnd is supposed to represent year and month it is almost certainly a better idea to use an actual date and formats to group/display things.