vlookup by most recent date

[toesockshoe]

I have 2 tables. I am trying to extract information from table 2 using the date value from table 1 and finding the most recent corresponding date on table 2 (that's less than the date on table 1). I'm not sure how to add tables on this forum, so please advice me, but I will type a rough outline of the table here.

Lets assume we're talking about applying discounts to a gym membership.

Table 1)

Customer   State    Date Joined

Adam  CA   1/1/2017

Carllos TX 1/1/2018

Adam CA 1/1/2019

Table 2 Discounts

State   Date Effective   Discount

CA 1/1/2015  5%

CA 1/1/2018  10%

TX 1/1/2014  2%

TX 1/1/2017 3%

TX 1/1/2019 4%

The final table should look like this:

Customer   State    Date Joined   Effective Discount

Adam  CA   1/1/2017    5%

Carllos TX 1/1/2018      3%

Adam CA 1/1/2019     10%

[r_behata]

Data table1;
input customer $ state $ doj :ddmmyy10.;
format doj ddmmyy10.;
cards;
Adam CA 1/1/2017
Carllos TX 1/1/2018
Adam CA 1/1/2019
run;

data table2;
input State $ Date_Effective :ddmmyy10.  Discount;
format Date_Effective ddmmyy10.;
cards;
CA 1/1/2015  5
CA 1/1/2018  10
TX 1/1/2014  2
TX 1/1/2017  3
TX 1/1/2019  4
run;

proc sort data=table2;
	by State descending Date_Effective;
run;

data table3;
	Set table1 ;
	do i=1 to nbs;
		set table2(rename=(state=state_ )) nobs=nbs point=i;
		if state=state_ and Date_Effective <=doj then do;
			output;	leave;	
		end;
	end;	
	drop state_ Date_Effective;
run;

[novinosrin]

data one;
input Customer $  State $   DateJoined :mmddyy10.;
format DateJoined mmddyy10.;
cards;
Adam  CA   1/1/2017
Carllos TX 1/1/2018
Adam CA 1/1/2019
;


data two;
input State $  DateEffective  :mmddyy10. Discount :percent.;
format DateEffective  mmddyy10. discount percent5.;
cards;
CA 1/1/2015  5%
CA 1/1/2018  10%
TX 1/1/2014  2%
TX 1/1/2017 3%
TX 1/1/2019 4%
;

proc sql;
create table want as
select a.*,Discount
from one a, two b
where a.state=b.state and a.DateJoined>=DateEffective
group by customer,a.state,datejoined
having max(DateEffective)=DateEffective
order by datejoined;
quit;

[toesockshoe]

thanks for your response.... do you mind explaining the logic on the where statement? How come there is no join anywhere... sorry I'm just picking up SAS after a year and my knowledge is pretty raw

[novinosrin]

Hello @toesockshoe 

The syntax is equivalent to

proc sql;
create table want1 as
select a.*,Discount
from one a left join two b
on a.state=b.state and a.DateJoined>=DateEffective
group by customer,a.state,datejoined
having max(DateEffective)=DateEffective
order by datejoined;
quit;