group within a group

nickspencer — Mon, 25 Jun 2018 03:06:13 GMT

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

Re: group within a group

novinosrin — Mon, 25 Jun 2018 03:33:20 GMT