group within a group

[nickspencer]

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

[novinosrin]

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;