topic Re: vlookup by most recent date in New SAS User

vlookup by most recent date

toesockshoe — Thu, 01 Aug 2019 17:58:34 GMT

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%

Re: vlookup by most recent date

r_behata — Thu, 01 Aug 2019 18:31:39 GMT

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;

Re: vlookup by most recent date

novinosrin — Thu, 01 Aug 2019 18:34:19 GMT


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;

Re: vlookup by most recent date

toesockshoe — Thu, 01 Aug 2019 20:22:15 GMT

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

Re: vlookup by most recent date

novinosrin — Thu, 01 Aug 2019 21:01:24 GMT

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;