topic Re: Aggregate data by group in SAS Procedures

Aggregate data by group

Miracle — Thu, 21 Mar 2013 07:53:27 GMT

Hi there.

I'd like to ask if I could achieve from A to B in just one single datastep instead of what I've done below( a datastep and sql)?

Thank you very much.

data A;

set tmp;

by id;

n+1;

if first.idthen n=1;

run;

proc sql;

create table B as

select *, count(id) as total

from A

group by id;

run;

quit;

id val n

1 11 1

1 22 2

1 33 3

2 22 1

id val n total

1 11 1 3

1 22 2 3

1 33 3 3

2 22 1 1

Re: Aggregate data by group

LinusH — Thu, 21 Mar 2013 08:37:23 GMT

Can't really see that you could, maybe using some advanced read-ahead programming, or using hash objects.

But why, is there a real problem, or optimizing for fun?

Re: Aggregate data by group

Miracle — Thu, 21 Mar 2013 09:35:14 GMT

Hi Linus. Thanks for your prompt reply.

I'm just wondering may be there are other more elegant coding ways to achieve this than what I did.

Re: Aggregate data by group

Astounding — Thu, 21 Mar 2013 13:37:11 GMT

Well, you can in a way. You still have to read the data twice, but you can accomplish that in a single DATA step:

data B;

n=0;

total=0;

do until (last.id);

set A (keep=id);

by id;

total + 1;

end;

do until (last.id);

set A;

by id;

n + 1;

output;

end;

run;

Each SET statement reads the entire data set independently of the other SET statement. So for each ID, the top loop counts the number of records, and the bottom loop reads the same records, calculates N, and outputs.

Re: Aggregate data by group

Miracle — Thu, 21 Mar 2013 23:05:27 GMT

Hi Astounding.

Thanks for your input.
It's always great to know other coding ways.

Re: Aggregate data by group

robertrao — Fri, 22 Mar 2013 18:47:34 GMT

I have a question...

Why the upper half is taking only the last occurance number.......

whereas the lower half of the code is taking all the counts even though we said do until(last.id) for both of them??

IS IT THE OUTPUT STATEMENT WHICH IS MAKING THE DIFFERENCE FOR THE BOTTOM HALF ALONE TO DISPLAY THE 1,2,3 instead of just 3!!1

Re: Aggregate data by group

Haikuo — Fri, 22 Mar 2013 19:10:23 GMT

It probably takes more than a brief post to explain the whole nine yard. The structure that Astounding used is call DOW, there are lots of histories around it. Google SAS DOW, you will know.

Here is one paper by Dorfman, who participated this part of history:

http://support.sas.com/resources/papers/proceedings09/038-2009.pdf

Haikuo

Re: Aggregate data by group

Peter_C — Sat, 23 Mar 2013 18:46:41 GMT

Although it appears that the data set is read twice, working with a BY statement, it is probable that the required buffers or pages of the data set, will be in memory when the second SET is performed - so unless BY groups are very large, performance is unlikely to be affected.

Re: Aggregate data by group

Peter_C — Sat, 23 Mar 2013 18:50:46 GMT

robertrao

read the paper offered by Haikuo to find the explanation.

http://support.sas.com/resources/papers/proceedings09/038-2009.pdf

Re: Aggregate data by group

Tal — Sat, 23 Mar 2013 22:45:04 GMT

so after SAS loops through the 1st by group it moves to the second loop,right,is that how DOWs work?

Re: Aggregate data by group

art297 — Sat, 23 Mar 2013 22:52:02 GMT

Yes! Then, at the end of the last loop, it goes back to the beginning to get the next level of the by variable(s).

Re: Aggregate data by group

Peter_C — Sat, 23 Mar 2013 23:32:36 GMT

Tal

what you describe is the way this program works ("after SAS loops through the 1st by group it moves to the second loop")

In general, DoW loops place a DO loop around a SET statement

The program here is more complex, having two interrelated DoW loops.

peterC

Re: Aggregate data by group

Tal — Sun, 24 Mar 2013 00:20:40 GMT

thanks guys