topic Sum and Count Question in New SAS User

Sum and Count Question

AJ_Brien — Mon, 22 Apr 2019 16:07:16 GMT

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!

Basic Sum and Count Question

AJ_Brien — Mon, 22 Apr 2019 16:08:36 GMT

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!

Re: Basic Sum and Count Question

Tom — Mon, 22 Apr 2019 16:40:19 GMT

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;

Re: Basic Sum and Count Question

AJ_Brien — Mon, 22 Apr 2019 16:43:38 GMT

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.

Re: Sum and Count Question

Astounding — Mon, 22 Apr 2019 16:55:20 GMT

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?

Re: Basic Sum and Count Question

Tom — Mon, 22 Apr 2019 16:59:15 GMT

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;

Re: Basic Sum and Count Question

AJ_Brien — Mon, 22 Apr 2019 17:14:50 GMT

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?

Re: Basic Sum and Count Question

AJ_Brien — Mon, 22 Apr 2019 18:54:16 GMT

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;

Re: Basic Sum and Count Question

Tom — Mon, 22 Apr 2019 19:10:35 GMT

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.