topic Re: Error statement show in proc sql 'sum' statement? in SAS Programming

Error statement show in proc sql 'sum' statement?

ybz12003 — Tue, 13 Jun 2017 15:10:47 GMT

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,

-------

200

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

---------

200

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

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

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;

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

Reeza — Tue, 13 Jun 2017 15:16:43 GMT

You can't put WHERE inside a SUM function.

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

ballardw — Tue, 13 Jun 2017 16:01:40 GMT

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.

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

ybz12003 — Tue, 13 Jun 2017 16:49:26 GMT

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;

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

Reeza — Tue, 13 Jun 2017 17:18:01 GMT

@ybz12003 wrote:

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

What that does mean?

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

ybz12003 — Tue, 13 Jun 2017 17:22:11 GMT

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

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

Reeza — Tue, 13 Jun 2017 17:29:25 GMT

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;

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

ballardw — Tue, 13 Jun 2017 19:37:50 GMT

What does your input data look like?

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

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

ybz12003 — Tue, 13 Jun 2017 20:52:30 GMT

Thanks for all of your kind help.