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!
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!
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;
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.
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;
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?
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;
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.