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 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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.