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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ
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

7 REPLIES 7
Jaheuk
Obsidian | Level 7

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

Reeza
Super User

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

Cynthia_sas
SAS Super FREQ
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
ballardw
Super User

@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;
Jaheuk
Obsidian | Level 7

 

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

Cynthia_sas
SAS Super FREQ
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
ballardw
Super User

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.

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, 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
  • 7 replies
  • 2355 views
  • 0 likes
  • 4 in conversation