Error statement show in proc sql 'sum' statement?

[ybz12003]

Hello:

I wrote some codes below.  The data are shown in the attachment.  Could someone help me to fix it?  Thanks.

proc contents data=contents out=have (keep=NAME);

run;

proc sql;

create table total as

select

sum(where 'Gm_go' in name) as N1,

sum(where 'gm_hhrt' in name) as N2,

sun(where 'gm_certificate' in name) as N3

from have;

quit;

However, error messages are shown in the log.

55 proc sql;

56 create table total as

57 select

58 sum(where 'Gm_go' in name) as N1,

                         -------

                           22

                          200

59 sum(where 'gm_hhrt' in name) as N2,

                        ---------

                         22

                        200

60 sun(where 'gm_certificate' in name) as N3

                        ----------------

                           22

                          200

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, ), *, **, +, ',', -, '.', /,

<, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE,

LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

ERROR 200-322: The symbol is not recognized and will be ignored.

61 from have;

62 quit;

[Reeza]

You can't put WHERE inside a SUM function. 

[ballardw]

And SUM needs a variable to sum.

You might look into proc means or summary:

Proc summary data=have nway;

   class name;

   var <what ever variable you want the sum of goes here>

   output out=havesum sum=;

run;

creates a sum of the variable for each level of the NAME variable.

If you really must have the data in wide form then Transpose the data.

[ybz12003]

I generated the codes below based on your suggesion.  However, I found the Num1-Num3 is not the total counts of N1-N3.  How to fix it?

Proc summary data=have nway;

   class name;

   var N1-N3;

   output out=havesum sum=Num1-Num3;

run;

[Reeza]

---

@ybz12003 wrote:

 However, I found the Num1-Num3 is not the total counts of N1-N3.  How to fix it?

---

What that does mean?

[ybz12003]

The N1-N3 showes all the numbers '1'.  It doesn't show the total of Num1=5, Num2=3, Num3=7.

[ballardw]

What does your input data look like?

We have no idea what your HAVE data set looks like at this time.

[Reeza · Accepted solution]

Did your proc run? I would expect it to error out actually.

If you want counts, use PROC FREQ, especially if the numbers are all 1. 

Summarizing data is a basic task. Read one of the papers that introduce you to PROC MEANs.

Taking 15 minutes to read a paper will result in saving at least 20 minutes for each of your questions, times 2-3 questions a day = 1 hour a day at least. 

http://lexjansen.com/search/searchresults.php?q=Proc%20means%20intro

Proc tabulate data=have;
Class name;
var n1 n2 n3;
Table (name all), (n1 n2 n3)*sum;
Run;

Note that you're also summarizing by NAME variable. Make sure you have multiple records per name, otherwise this won't make sense 

Proc means data=have nway;
Class name;
Var n1 n2 n3;
Output out=want sum = /autoname;
Run;

[ybz12003]

Thanks for all of your kind help.