DATA Step, Macro, Functions and more

Proc tabulate- Remove SUM

Reply
Contributor
Posts: 42

Proc tabulate- Remove SUM

Hi,

My proc tabulate is summing up values, any idea how I stop this from happening please?
Super User
Posts: 6,935

Re: Proc tabulate- Remove SUM

Posted in reply to Brandon16

Yes ... change the program.

 

If you show us the program, we can tell you what to change.  

Contributor
Posts: 42

Re: Proc tabulate- Remove SUM

Posted in reply to Brandon16
Data jw_all;
Set jw_sum
Gift_l
Supp_l;
Run;

Proc sort data jw_all; by final_var; run;

The variable final_var groups up as a total on my tabulate.
Super User
Posts: 6,935

Re: Proc tabulate- Remove SUM

Posted in reply to Brandon16

Seriously?  

 

If PROC TABULATE is generating the wrong report, you need to show the PROC TABULATE step.

Contributor
Posts: 42

Re: Proc tabulate- Remove SUM

Posted in reply to Brandon16
Proc tabulate data jw_all missing;
Class final_var;
Var mnth1 mnth2 mnth3;
Table (jw_all=""),
(Mnth1="")
(Mnth2="")
(Mnth3="") / box="final"
Run;

SAS Super FREQ
Posts: 9,434

Re: Proc tabulate- Remove SUM

Posted in reply to Brandon16

Hi:

  I'm not sure what you want. If you just want to get rid of the label for the SUM statistic, investigate the use of the KEYLABEL statement. If you mean you want some other statistic other than SUM, then investigate the use of the other KEYWORD statistics, like MIN, MAX, MEAN, MEDIAN, CSS, STD, PROBT, etc.

 

  PROC TABULATE is a summary/summarizing procedure that produces crosstabular results. It MUST put something, some calculated number in the crossing cells. If you only have a CLASS statement the default statistic to fill those cells is the N or count statistic. If you have a VAR statement, then the default statistic is the SUM statistic. If you have both a CLASS and a VAR statement in the same program, then you get the SUM statistic, usually, depending on how you write your TABLE statement.

 

  I see some errors in your code. instead of this

Proc tabulate data jw_all missing;

I would expect to see this

Proc tabulate data=jw_all missing;

(note the = sign and assuming that jw_all is the name of your dataset.

 

Next, instead of this:

Table (jw_all=""),
(Mnth1="")
(Mnth2="")
(Mnth3="") / box="final"

that shows the name of your data set jw_all in the TABLE statement and that there is not a semi-colon at the end of the TABLE statement, I would instead expect to see you use FINAL_VAR in the TABLE statement, like this:

 
Table (final_var=""),
(Mnth1="")
(Mnth2="")
(Mnth3="") / box="final";
 

  This syntax would put FINAL_VAR in the row dimension and the SUM of MNTH1, MNTH2 and MNTH3 in the column dimension with the box in the upper left-hand side of table containing the word "final"; Something like this -- shown with FAKE data:

fake_jw_all.png

Here is the code I used to create that table:

data jw_all;
  infile datalines;
  input final_var mnth1 mnth2 mnth3;
return;
datalines;
1 1 2 3
1 2 3 4
1 3 4 5
1 4 5 6
2 5 6 7
2 6 7 8
2 7 8 9
3 8 9 0
3 9 8 7
3 7 6 5
;
run;

proc tabulate data=jw_all;
class final_var;
var mnth1 mnth2 mnth3;
Table (final_var=""),
(Mnth1="")
(Mnth2="")
(Mnth3="") / box="final";
run;

Note that the syntax construct of mnth1=" " only serves to get rid of the column header with the variable NAME or label. It is NOT, as shown, getting rid of the keyword SUM for the name of the statistic being displayed in the data cells. That is the job of the KEYLABEL statement. However, if you're going to get rid of SUM, then I would suggest a revised program so that you had only this for your TABLE statement:

Table (final_var=""),
 Mnth1 Mnth2 Mnth3  / box="final";

Then, if you add the KEYLABEL statement, you will have the variable names as column headers without seeing the word SUM.

 

cynthia

 

Ask a Question
Discussion stats
  • 5 replies
  • 428 views
  • 0 likes
  • 3 in conversation