DATA Step, Macro, Functions and more

group within a group

Reply
Occasional Contributor
Posts: 18

group within a group

[ Edited ]

Hi,

 

I have a Teradata table, the data from which I need to group and insert into another Teradata table using SAS. The data in table HAVE looks as below.

 

TYPE       EMPNO           DATE              TRAN_ID          INDICATOR

001           123                  6/01/2018         2                          A

001            123                6/05/2018          1                          A

001            123                6/03/2018          5                          A

001            234                6/12/2018          3                          A

 

 

The Teradata table  HAVE I need to insert into has the following columns.

 

TYPE        EMPNO      DATE            TRAN_ID      COUNT

 

I need to group the records from HAVE table and insert into the FINAL table where indicator=A and type=001 and group the records by TYPE and EMPNO. The DATE should have the current date. But the issue I am having is with the TRAN_ID, it should have the tran_id of max(DATE) for each TYPE and EMPNO for eg. in case of emp 123 max date is 6/05 and its TRAN_ID is 1. It should look like this.

 

TYPE        EMPNO      DATE            TRAN_ID  COUNT

001             123            6/24/2018        1                3

001              234           6/24/2018        3                1

 

I hope this makes sense. And Thanks in advance.

 

Note: I am able to group other fields except the tran_id like this

 

proc sql;

insert into table.final

select a.TYPE,

          a.EMPNO,

          current_date as DATE,

          a.COUNT

from 

(select type,

         empno, count(*) as COUNT from table.HAVE where indicator='A' and type=001 group by 1,2) a;

quit;

 

 

**??? tran_id

PROC Star
Posts: 1,817

Re: group within a group

[ Edited ]
Posted in reply to nickspencer

 

proc sql;

insert into table.final

select a.TYPE,

          a.EMPNO,

          current_date as DATE,

          a.COUNT

from 

(select type,

         empno, count(*) as COUNT from table.HAVE group by 1,2 having indicator='A' and date=max(date)) a ;

quit;

 

Ask a Question
Discussion stats
  • 1 reply
  • 64 views
  • 0 likes
  • 2 in conversation