DATA Step, Macro, Functions and more

obtaining a summary row after individual rows in sql

Reply
Occasional Contributor
Posts: 15

obtaining a summary row after individual rows in sql

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;

 

a1

a2

a2

b1

b3

;

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;

 

Super User
Posts: 22,874

Re: obtaining a summary row after individual rows in sql

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 Smiley Sad

 

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

Ask a Question
Discussion stats
  • 1 reply
  • 70 views
  • 0 likes
  • 2 in conversation