Aggregate Transformation in SAS

[Helannivas]

Hi,

I have a source table as structure as below

identifer	payout	region
1	10	1
2	20	2
3	10	1
4	20	3
5	10	1
6	20	2
7	10	3
8	20	3
9	10	2
10	20	4
11	10	4
12	20	2

And my required output is 

region	count identifier	sum payout
1	3	30
2	4	70
3	3	50
4	2	30

What transformation in SAS Data Integrator studio  have to use for the required result?

Is there any aggregartor transformation in SAS DI??

Awaiting response..Thanks in advance

[Kurt_Bremser]

I do not work with DI Studio, but the SQL code is

proc sql;
create table want as
select
  region,
  count(distinct id) as count_id,
  sum(payout) as sum_payout
from have
group by region
;
quit;

[Helannivas]

Thanks Kurt for your reply..am aware of proc sql statement....Require help in SAS DI..

[Kurt_Bremser]

AFAIK, DI Studio has the option to create a node with custom code.

And I'd expect it to have a query builder similar to the one in Enterprise Guide (the documentation tells me that this is the case).

Recreating my code with the EG Query Builder was done in less than a minute:

- select all three columns

- add the SUM summary function to payout

- add the COUNT DISTINCT summary function to id

- group by region (happens automatically in EG QB)

- adapt the column names to your needs

[LinusH]

Custom code in DI should be avoided at all (ok, not all, but close to) costs.

Bu like @Kurt_Bremser is reflecting about standard functionality in EG, it's also a standard transformation in DI Studio that should be used. My primary options i SQL Join (no, you don't have to do join in this transformation, just remove the second source table connector).

You can see in the online doc, or in the product help section on how to use it.

[Patrick]

@Helannivas

Using the SQL Join transformation with a single table as input like@LinusH proposes should make it quite simple to get the result you want to.

[Riteshdell · Accepted solution]

1)- Source table

 

2)- extract transformation 

3)- mapping , use sum and count function:

4)- groupy by tab, select region as group by

5) go to option tab- select "yes" to distinct.

6) - output data same-