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

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Riteshdell
Quartz | Level 8

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

6 REPLIES 6
Kurt_Bremser
Super User

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
Quartz | Level 8

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

Kurt_Bremser
Super User

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
Tourmaline | Level 20

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.

Data never sleeps
Patrick
Opal | Level 21

@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
Quartz | Level 8

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

 

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!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 3795 views
  • 2 likes
  • 5 in conversation