topic Re: Aggregate Transformation in SAS in SAS Data Management

Aggregate Transformation in SAS

Helannivas — Wed, 03 May 2017 07:18:04 GMT

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

Re: Aggregate Transformation in SAS

Kurt_Bremser — Wed, 03 May 2017 08:34:08 GMT

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;

Re: Aggregate Transformation in SAS

Helannivas — Wed, 03 May 2017 08:47:11 GMT

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

Re: Aggregate Transformation in SAS

Kurt_Bremser — Wed, 03 May 2017 09:12:08 GMT

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

Re: Aggregate Transformation in SAS

LinusH — Wed, 03 May 2017 10:00:46 GMT

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.

Re: Aggregate Transformation in SAS

Patrick — Wed, 03 May 2017 12:52:09 GMT

@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.

Re: Aggregate Transformation in SAS

Riteshdell — Fri, 05 May 2017 20:38:44 GMT

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-