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%
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;
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;
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.