obtaining a summary row after individual rows in sql

Batman — Wed, 15 Nov 2017 21:50:08 GMT

The code below creates a unique count of the variable y for each value of x and a summary row. However, it requires two queries and a data step. Is there a way to all in one query?

/*sample data*/

data a;

input x $1. y 1.;

datalines;

;

run;

proc sql;

/*first query*/

create table b as select x, n(distinct y) as y_cnt

from a

group by x;

/*second query*/

create table c as select 'Total' as x, n(distinct y) as y_cnt

from a;

quit;

data d;

length x $5;

/*append queries*/

set b c;

run;

Re: obtaining a summary row after individual rows in sql

Reeza — Wed, 15 Nov 2017 22:29:33 GMT

Do your records overlap within X/ by group?

ie If you sum the total from the summary total are you expecting it to add to the total, or do you need a separate distinct count because of the overlap?

If you need that distinction I can't think of an alternative 😞

Hopefully someone else can, so partially responding to see if there is a solution to this problem.

topic Re: obtaining a summary row after individual rows in sql in SAS Programming

obtaining a summary row after individual rows in sql

Re: obtaining a summary row after individual rows in sql