topic Link between 3 tables - and no register found in SAS Enterprise Guide

Link between 3 tables - and no register found

Ovaty — Tue, 14 May 2019 12:59:27 GMT

Hi guys, good morning!

I have 3 tables like below (t1, t2 and t3) t4 is the result that I want to do.

How can I link t1 -> t2 -> t3, when the t1.anomes is not found in t2.anomes.

In this case I must consider the current month (201905-2019 Q2).

After that I find the cars in t3, based on t2.quarter (2019 Q2).

t1	t2		t3		t4 result
anomes	anomes	quarter	quarter	car	t1.anomes	t3.car
201810	201901	2019 Q1	2019 Q1	BMW	201810	Nissan
	201902	2019 Q1	2019 Q1	MB	201810	Ferrari
	201903	2019 Q1	2019 Q1	Posrche	201810	Audi
	201904	2019 Q2	2019 Q1	Tesla	201810	VW
	201905	2019 Q2	2019 Q1	Jaguar	201810	Mazda
	201906	2019 Q2	2019 Q2	Nissan
	201907	2019 Q3	2019 Q2	Ferrari
	201908	2019 Q3	2019 Q2	Audi
	201909	2019 Q3	2019 Q2	VW
	201910	2019 Q4	2019 Q2	Mazda
	201911	2019 Q4
	201912	2019 Q4

Thanks a lot !

Gustavo.

Re: Link between 3 tables - and no register found

PeterClemmensen — Tue, 14 May 2019 13:05:07 GMT

What if 201810 was actually present in t2?

Re: Link between 3 tables - and no register found

Ovaty — Tue, 14 May 2019 13:26:57 GMT

If 201810 was the present month, we have to consider that.

The rule is: t1.anomes is the main KEY.

If I found the similar date in t2, great.

But if I did't find similar date in t2, I must consider the present month in t2.

@PeterClemmensen wrote:
What if 201810 was actually present in t2?

Re: Link between 3 tables - and no register found

Kurt_Bremser — Thu, 16 May 2019 07:39:35 GMT

Start out by making your data intelligent. Right now it's outright "stupid". See Maxim 33.

Values that deal with dates and times should be stored as such, so the mountain of SAS functions, formats and other tools for such values can be used.

Therefore t1 will look like this:

data t1;
input anomes yymmdd10.;
format anomes yymmn6.;
datalines;
2018-10-01
2019-02-01
;

Next, we don't need t2 at all, as SAS provides a format that converts dates to quarters on the fly.

t3 will look like this:

data t3;
input quarter yymmdd10. car :$10.;
format quarter yyq6.;
datalines;
2019-01-01 BMW
2019-01-01 MB
2019-01-01 Porsche
2019-01-01 Tesla
2019-01-01 Jaguar
2019-04-01 Nissan
2019-04-01 Ferrari
2019-04-01 Audi
2019-04-01 VW
2019-04-01 Mazda
;

The code for the join will be this:

%let cur_qu=%sysfunc(today(),yyq6.);

proc sql;
create table want as (
select t1.anomes,t3.car
from t1 left join t3
on put(anomes,yyq6.) = put(quarter,yyq6.)
where t3.quarter ne .
union all
select a.anomes,b.car
from (
  select t1.anomes
  from t1 left join t3
  on put(t1.anomes,yyq6.) = put(t3.quarter,yyq6.)
  where t3.quarter = .
) a,
t3 b
where put(b.quarter,yyq6.) = "&cur_qu"
)
order by anomes
;
quit;

The first select finds the matches, the second finds the non-matches and joins them to the current quarter.

As you can see, I added a second date in t1 to illustrate that the code works for both possibilities.

The resulting table will be this (result of proc print):

anomes    car

201810    Nissan 
201810    Ferrari
201810    Audi   
201810    VW     
201810    Mazda
201902    Porsche
201902    Jaguar 
201902    MB     
201902    BMW    
201902    Tesla

Note how data steps with datalines are used to create example data. This is the required way to post data here so that answers can be quickly found and tested.