DATA Step, Macro, Functions and more

PCT in PROC TABULATE

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 89
Accepted Solution

PCT in PROC TABULATE

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:

 

Capture.PNG

 


Accepted Solutions
Solution
‎08-04-2017 09:36 AM
SAS Super FREQ
Posts: 8,864

Re: PCT in PROC TABULATE

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

View solution in original post


All Replies
Frequent Contributor
Posts: 89

PCT in PROC TABULATE: part 2

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

 

Capture.PNG

Super User
Posts: 19,782

Re: PCT in PROC TABULATE

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

SAS Super FREQ
Posts: 8,864

Re: PCT in PROC TABULATE

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
Super User
Posts: 11,343

Re: PCT in PROC TABULATE


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;
Frequent Contributor
Posts: 89

Re: PCT in PROC TABULATE

 

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.

 

Capture.PNG

 

 

 

 

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
‎08-04-2017 09:36 AM
SAS Super FREQ
Posts: 8,864

Re: PCT in PROC TABULATE

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
Super User
Posts: 11,343

Re: PCT in PROC TABULATE

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 184 views
  • 0 likes
  • 4 in conversation