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:
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
@Jaheuk I've merged these posts together as they're related.
@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;
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.