Summarize data using SQL

[MagD]

Hi All,

I am still very new to SAS. Please help.

I am trying to sum my data by using the below code but it isn't working. I tried adding having 'Transaction Date'n = max('Transaction Date'n) but it only takes the maximum number and not both numbers.  

I want for example 2, 116.9 (the sum of Amount) but I am getting 2 different amounts.

proc sql;
create table Payments
as select distinct *, sum(Amount) as Amount
from transaction
group by 'Matter Number'n;
run;

[ballardw]

Likely you are getting something related to use of DISTINCT *.

If you want to group by a variable then typically the only variables in a query should be the group by variables and those summarized.

[PaigeMiller]

---

@MagD wrote:

I am trying to sum my data by using the below code but it isn't working. 

---

What isn't working? Please be specific.

Also, please provide your data so we can try your code and see for ourselves, by following these directions: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

Lastly, since we are trying to help you, please help by modifying the Subject of your post to something meaningful.

[Kurt_Bremser]

Please supply example data in usable form (in a data step with datalines), and what you expect out of it,

[MagD]

Hi,

proc sql;
create table Payments
as select *, sum(Amount) as Amount
from transaction_import_prep
group by 'Matter Number'n
;
run;

Data looks like:

Name	Date	Amount
Peter	15-May	899
Susan	15-May	200
Peter	16-May	1217
Susan	16-May	500

I am trying to get the following 

Name	Date	Amount
Peter	16-May	2116
Susan	16-May	700

[PaigeMiller]

DATE is ignored in getting the proper output?

[MagD]

No. The date should be considered in the output.

[Kurt_Bremser]

Simple use of summary functions in SQL:

data have;
input Name $ Date :date9. Amount;
format date date9.;
datalines;
Peter 15May2019 899
Susan 15May2019 200
Peter 16May2019 1217
Susan 16May2019 500
;

proc sql;
create table want as
select
  name,
  max(date) format=date9. as date,
  sum(amount) as amount
from have
group by name;
quit;

[Tom]

There are two issues with your query.

The main issue is that you are selecting variables, like DATE and AMOUNT, that are neither part of the GROUP BY list or the result an aggregate function, like SUM(), MAX(), etc.   So you will get out the same number of observations you put in, instead of getting only the unique combinations of the GROUP BY variables.  SAS will calculate the aggregated values and re-merge them onto the detail records.

The second issue is probably what is making it hard to notice the first issue. You are selecting two columns named AMOUNT.  If you just do that in a report (SELECT without CREATE TABLE) then SAS will display both columns. But when it has to put the result into a dataset it cannot make two variables with the same name.  What PROC SQL will do in that case is store the FIRST column and ignore the second one with the same name.

So make sure that all column are either part of the GROUP BY or the result of an aggregate function.

create table Payments as 
select
  'Matter Number'n
 , max(Date) as Date format=DATE9.
 , sum(Amount) as Amount
from transaction_import_prep
group by 'Matter Number'n
;

Or just use regular SAS code instead of SQL.

proc summary data=transaction_import_prep nway missing;
  class 'Matter Number'n;
  var Date Amount ;
  output out=Payments max(Date)=Date sum(Amount)=Amount;
run;

[MagD]

Hi,

Thank yo so much. It worked!