SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Aggregate Transformation in SAS

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 101
Accepted Solution

Aggregate Transformation in SAS

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

 

 


Accepted Solutions
Solution
‎05-07-2017 11:35 AM
Contributor
Posts: 68

Re: Aggregate Transformation in SAS

Posted in reply to Helannivas

1)- Source table

 2.JPG

2)- extract transformation 

 

1.JPG

 

3)- mapping , use sum and count function:

3.JPG

 

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

 

4.JPG

 

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

 

5.JPG

 

6) - output data same- 

 

6.JPG

 

View solution in original post


All Replies
Super User
Posts: 7,808

Re: Aggregate Transformation in SAS

Posted in reply to Helannivas

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 101

Re: Aggregate Transformation in SAS

Posted in reply to KurtBremser

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

Super User
Posts: 7,808

Re: Aggregate Transformation in SAS

Posted in reply to Helannivas

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,430

Re: Aggregate Transformation in SAS

Posted in reply to KurtBremser

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

Bu like @KurtBremser 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.

Data never sleeps
Respected Advisor
Posts: 4,173

Re: Aggregate Transformation in SAS

Posted in reply to Helannivas

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

Solution
‎05-07-2017 11:35 AM
Contributor
Posts: 68

Re: Aggregate Transformation in SAS

Posted in reply to Helannivas

1)- Source table

 2.JPG

2)- extract transformation 

 

1.JPG

 

3)- mapping , use sum and count function:

3.JPG

 

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

 

4.JPG

 

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

 

5.JPG

 

6) - output data same- 

 

6.JPG

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 283 views
  • 1 like
  • 5 in conversation