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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.