topic Re: PCT in PROC TABULATE in SAS Programming

PCT in PROC TABULATE

Jaheuk — Wed, 02 Aug 2017 13:43:45 GMT

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:

PCT in PROC TABULATE: part 2

Jaheuk — Wed, 02 Aug 2017 14:10:37 GMT

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

Re: PCT in PROC TABULATE

Reeza — Wed, 02 Aug 2017 14:40:05 GMT

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

Re: PCT in PROC TABULATE

Cynthia_sas — Wed, 02 Aug 2017 14:49:34 GMT

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

Re: PCT in PROC TABULATE

ballardw — Wed, 02 Aug 2017 16:01:47 GMT

@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;

Re: PCT in PROC TABULATE

Jaheuk — Thu, 03 Aug 2017 14:21:43 GMT

dear all,

sorry for all confusions,

below table like it should be and

code to create the datasets in 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

Re: PCT in PROC TABULATE

Cynthia_sas — Thu, 03 Aug 2017 15:09:02 GMT

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

Re: PCT in PROC TABULATE

ballardw — Thu, 03 Aug 2017 22:44:58 GMT

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.