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;
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;
You can't put WHERE inside a SUM function.
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.
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;
@ybz12003 wrote:
However, I found the Num1-Num3 is not the total counts of N1-N3. How to fix it?
What that does mean?
The N1-N3 showes all the numbers '1'. It doesn't show the total of Num1=5, Num2=3, Num3=7.
What does your input data look like?
We have no idea what your HAVE data set looks like at this time.
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;
Thanks for all of your kind help.
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.