BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Beto16
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20
Or simple SQL as you started with, just drop the distinct and fix the misplaced semicolons and parantheses.
Data never sleeps

View solution in original post

7 REPLIES 7
Reeza
Super User

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

ballardw
Super User

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.

Beto16
Obsidian | Level 7

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 

 

Reeza
Super User

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

Beto16
Obsidian | Level 7

 

 

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 

 

 

 

 

 

 

 

Reeza
Super User

Use proc means.

 

proc means data=have;
class id;
var amt amt1 amt2;
output out=want sum=/autoname;
run;
LinusH
Tourmaline | Level 20
Or simple SQL as you started with, just drop the distinct and fix the misplaced semicolons and parantheses.
Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1599 views
  • 0 likes
  • 4 in conversation