BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Ronein
Onyx | Level 15

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;
1 ACCEPTED SOLUTION

Accepted Solutions
SASJedi
Ammonite | Level 13

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;
Check out my Jedi SAS Tricks for SAS Users

View solution in original post

1 REPLY 1
SASJedi
Ammonite | Level 13

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;
Check out my Jedi SAS Tricks for SAS Users

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 674 views
  • 1 like
  • 2 in conversation