Sum and Count Question

[AJ_Brien]

Hello,

this is the sample data, where column last3 is char. 

last3	COUNT
434	1
543	1
558	1
575	1
675	2
749	1
757	1
787	1
934	1
948	1
956	2

the output I'm looking to get is the one that gives me a sum of all the counts

proc sql;
create table TRIAL as
  select last3,      
  count(*) as COUNT, sum(COUNT)
  from SAMPLE
  group by last3;  
quit;

:

last3	COUNT
434	1
543	1
558	1
575	1
675	2
749	1
757	1
787	1
934	1
948	1
956	2
total	11

But I'm receiving no output for this query. My log below:

1 %_eg_gridprologue;
NOTE: Remote submit to GRID commencing.
NOTE: Remote submit to GRID complete.
2 %_eg_gridclientepilogue;
3 %let _EGRC=&_EGRCGRID;
4

Thanks in advance!

[Astounding]

What output did you expect? Should it be a report or a data set?

Is LAST3 long enough to hold "total"?

Did your boss say you must use SQL?

[AJ_Brien]

Hello,

this is the sample data, where column last3 is char. 

last3	COUNT
434	1
543	1
558	1
575	1
675	2
749	1
757	1
787	1
934	1
948	1
956	2

the output I'm looking to get is the one that gives me a sum of all the counts

proc sql;
create table TRIAL as
  select last3,      
  count(*) as COUNT, sum(COUNT)
  from SAMPLE
  group by last3;  
quit;

:

last3	COUNT
434	1
543	1
558	1
575	1
675	2
749	1
757	1
787	1
934	1
948	1
956	2
total	11

But I'm receiving no output for this query. My log below:

1 %_eg_gridprologue;
NOTE: Remote submit to GRID commencing.
NOTE: Remote submit to GRID complete.
2 %_eg_gridclientepilogue;
3 %let _EGRC=&_EGRCGRID;
4

Thanks in advance!

[Tom]

It is not clear what you want to produce.  Do you just want that report?  PROC PRINT can sum a column for you.  PROC REPORT has even more options.

proc print data=sample;
  sum count;
run;

[AJ_Brien]

I'm unable to get the total sum of the counts at the bottom of the table. Currently I'm trying to use sum(count) to get that sum value, but it's not producing any output and giving no errors either. So I was wondering what change can I make to my code provided to get that sum at the bottom.

[Tom]

Why would you want to add an extra observation to the existing dataset?

What variable name would you use for the sum of the counts?  If you put it back into COUNT then any analysis you try to do with COUNT will be messed up by the extra observation. So you will need to keep adding WHERE clauses or other things to deal with it.

To just see the count and sum you could use your PROC SQL code without the CREATE clause.

proc sql;
select count(*) as number_of_rows,sum(count) as sum_of_count
from sample
;
quit;

To make a dataset with the summary data add the CREATE clause.  Then you could print it (or do other things with it).

proc sql;
create table summary as 
select count(*) as number_of_rows,sum(count) as sum_of_count
from sample
;
quit;
proc print data=summary;
run;

[AJ_Brien]

That totally makes sense.

When I try to do sum(count) though, it gives an error:

ERROR: The SUM summary function requires a numeric argument.

ERROR: The following columns were not found in the contributing tables: count.

since last 3 column is char, I'd assume the count(*) column is also char and when a sum is computed it hence gives an error. Is that what's happening?

[AJ_Brien]

I realized the aggregate sum function will not work with char variables. So instead of doing that, I'm thinking I'll just get the total number  of observation count and use that in my computation. This is the code I'm using for that purpose, but I'm still getting no output and no error!

data _NULL_;
	if 0 then set SAMPLE nobs=n;
	call symputx('nrows',n);
	stop;
run;
%put nobs=&nrows;

[Tom]

Your example log looked strange. It did not appear to have any actual user written code in it.  Just automatic stuff generated by Enterprise Guide.