topic Re: Select one row for each customer from Tera table in SAS Programming

Select one row for each customer from Tera table

Ronein — Thu, 04 Jan 2024 10:52:17 GMT

Hello

I want to select one row for each customer and this row should be the row with max date (Last date).

The source table is in Tera but I must run it in SAS.

Is there a better way to do it in one step?

Here I did it in 2 steps


proc sql;  
connect to teradata (server=dwprod schema=DWP_vall authdomain=TeraDataAuth);
create table t_Customer_Segment  as 
select * from connection to teradata
(
SELECT   Branch_Cust_Nbr,Branch_Nbr,Customer_Segment,DATE_UPDATED_TO_DW
from V01X997_IP_REGULATORY_ACTIVITY_SEGMENT
order by  Branch_Nbr,Branch_Cust_Nbr,DATE_UPDATED_TO_DW desc
);
disconnect from teradata;
quit ;

Data t_Customer_Segment_b;
set t_Customer_Segment;
by Branch_Nbr,Branch_Cust_Nbr;
If first.Branch_Cust_Nbr;
Run;

Re: Select one row for each customer from Tera table

SASJedi — Thu, 04 Jan 2024 13:12:57 GMT

This code does all of the work in Teradata and brings back only the desired rows to SAS. It might run faster for you:

proc sql;  
connect to teradata (server=dwprod schema=DWP_vall authdomain=TeraDataAuth);
create table t_Customer_Segment_b  as 
select * from connection to teradata
(
	select allRows.Branch_Cust_Nbr
		  ,allRows.Branch_Nbr
		  ,Customer_Segment
		  ,DATE_UPDATED_TO_DW
		from V01X997_IP_REGULATORY_ACTIVITY_SEGMENT as allRows
		inner join
		(select Branch_Cust_Nbr
			   ,Branch_Nbr
			   ,max(DATE_UPDATED_TO_DW) as TargetDate
			from V01X997_IP_REGULATORY_ACTIVITY_SEGMENT
			group by Branch_Cust_Nbr
					,Branch_Nbr) as maxDate
		on  allRows.Branch_Cust_Nbr=maxDate.Branch_Cust_Nbr
		and allRows.Branch_Nbr=maxDate.Branch_Nbr
		where DATE_UPDATED_TO_DW=TargetDate
)
;
disconnect from teradata;
quit;