Error statement show in proc sql 'sum' statement?

Accepted Solution Solved
Reply
Super Contributor
Posts: 274
Accepted Solution

Error statement show in proc sql 'sum' statement?

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;

 

Attachment

Accepted Solutions
Solution
‎06-13-2017 04:52 PM
Grand Advisor
Posts: 17,360

Re: Error statement show in proc sql 'sum' statement?

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;

View solution in original post


All Replies
Grand Advisor
Posts: 17,360

Re: Error statement show in proc sql 'sum' statement?

You can't put WHERE inside a SUM function. 

Grand Advisor
Posts: 10,211

Re: Error statement show in proc sql 'sum' statement?

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.

Super Contributor
Posts: 274

Re: Error statement show in proc sql 'sum' statement?

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;

Grand Advisor
Posts: 17,360

Re: Error statement show in proc sql 'sum' statement?


ybz12003 wrote:

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


What that does mean?

Super Contributor
Posts: 274

Re: Error statement show in proc sql 'sum' statement?

[ Edited ]

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

Grand Advisor
Posts: 10,211

Re: Error statement show in proc sql 'sum' statement?

What does your input data look like?

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

Solution
‎06-13-2017 04:52 PM
Grand Advisor
Posts: 17,360

Re: Error statement show in proc sql 'sum' statement?

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;
Super Contributor
Posts: 274

Re: Error statement show in proc sql 'sum' statement?

Thanks for all of your kind help.

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 127 views
  • 1 like
  • 3 in conversation