topic Sum group by in SAS Procedures

Sum group by

Beto16 — Wed, 09 Mar 2016 21:47:54 GMT

hi I have a table I need to sum the amt by id...

prorc sql;

(select

id,

date,

sum(distinct(amt) as amt,

sum(distinct(amt2) as amt2,

sum(distinct(amt3) as amt3

from table1;

group by id,date,amt, amt2,amt3

run;

there are dates that have 0 I would want to summarize by each Id

currently I get same total thru out

Re: Sum group by

Reeza — Wed, 09 Mar 2016 22:10:12 GMT

That's not close to working code...there are many syntax errors, mostly with open parentheses and not enough closing parentheses.

Can you please post some sample data and your actual code that doesn't work.

From the problem description it sounds like you have Dates that aren't included because of no records.

This is a common question, and I've heard the recommended solution is the PRELOADFMT within a summary proc, such as tabulate.

http://www.lexjansen.com/nesug/nesug11/cc/cc29.pdf

Re: Sum group by

ballardw — Thu, 10 Mar 2016 00:19:38 GMT

Your posted code has a lot of typos.

Are you sure you need all those Distinct predicates?

You might provide some example data and what you want the output to be.

Re: Sum group by

Beto16 — Wed, 09 Mar 2016 22:50:03 GMT

The data look like this

id. Amt. amt1 Amt2

1. 200. 500 300

1. 200. 500. 300

2. 200 500. 300

2. 200. 500. 300

i need

id. Amt. amt1. Amt2

1. 100. 250. 150

2. 100. 250. 150

the first summarize the whole col an I need it by id total

Re: Sum group by

Reeza — Wed, 09 Mar 2016 22:53:20 GMT

Please explain the logic behind that calculation. I'm not following how ID 1 goes to 100, 250 and 150

Re: Sum group by

Beto16 — Wed, 09 Mar 2016 23:20:35 GMT

id.machine Amt. amt1. Amt2

A1. 10. 20. 30

A1. 20. 40. 50

B2. 20. 60. 30

B3. 30. 70. 40

B3. 10. 120. 130

these are daily cash entries by machine id I need to sum an group by id.machine

id,machine. amt. amt1. Amt2

A1. 30. 60. 80

B2. 20. 60. 30

b3. 40. 190. 170

Re: Sum group by

Reeza — Wed, 09 Mar 2016 23:23:21 GMT

Use proc means.

proc means data=have;
class id;
var amt amt1 amt2;
output out=want sum=/autoname;
run;

Re: Sum group by

LinusH — Thu, 10 Mar 2016 07:11:19 GMT

Or simple SQL as you started with, just drop the distinct and fix the misplaced semicolons and parantheses.