## join two tables based on date

Solved
Frequent Contributor
Posts: 126

# join two tables based on date

This is an example, I want to join tables A and B to obtain table c. As you can see I want to join tables on caldt if table C did not find TNA for the correspnding date in B then it took the previous observation

Table A

caldt            TNA

21070327    20

20170328    50

20170329    120

20170330     50

20170331     87

Table B

caldt            mret

20170325     .

20170326     .

21070327    0.45

20170328    0.98

20170329    0.67

20170330     0.50

20170331     0.87

Table C

caldt            mret   TNA

20170325     .         20

20170326     .         20

21070327    0.45     20

20170328    0.98    50

20170329    0.67    120

20170330     0.50    50

20170331     0.87    87

Accepted Solutions
Solution
‎04-03-2017 02:29 PM
Super User
Posts: 9,599

## Re: join two tables based on date

[ Edited ]

Edit, I noted in your test data you had 2107 year values, I assume these were typed incorrectly and fixed them.

If the new data is true, then all you have to do is a minor update to table A before the merge:

```data A;
input caldt \$ TNA \$;
cards;
20170325 20
20170326 .
20170327 .
20170328 50
20170329 120
20170330 50
20170331 87
run;
data B;
input caldt \$ mret \$;
cards;
20170325 .
20170326 .
20170327 0.45
20170328 0.98
20170329 0.67
20170330 0.50
20170331 0.87
run;
data a;
set a;
retain lstv;
if tna ne "" then lstv=tna;
if tna="" then tna=lstv;
run;

proc sql;
create table C as
select  B.*,
A.TNA
from    A
right join B
on       A.CALDT=B.CALDT;
quit;```

However, I assume here that the dates are really character, and that the first obs get copied down.  What your probably after, which is slightly different, is to take a baseline value and use that for missing elements.  In which case in your baseline data, create a flag (or logic) and pull that in and do a case when, for example if the first date was to be used for missings\;

```proc sql;
create table C as
select  B.*,
case when A.TNA is null then X.TNA else A.TNA end as TNA
from    A
right join B
on       A.CALDT=B.CALDT
left join (select TNA from A having input(CALDT,yymmdd10.)=min(input(CALDT,yymmdd10.))) X
on      1=1;
quit;```

All Replies
Super User
Posts: 9,599

## Re: join two tables based on date

Post test data in the form of a datastep to get working code, best guess:

```proc sql;
create table C as
select  A.*,
B.TNA
from    A
right join B
on       A.CALDT=B.CALDT;
quit;```
Frequent Contributor
Posts: 126

## Re: join two tables based on date

Your program did not give me the right table C. It create a lot of "."

Super User
Posts: 9,599

## Re: join two tables based on date

Post test data in the form of a datastep.

See other posts here, as noted I do not have time to enter your test data or guess if its the same strucutre or not.

Frequent Contributor
Posts: 126

## Re: join two tables based on date

data A;
input caldt \$ TNA \$;
cards;
21070327 20
20170328 50
20170329 120
20170330 50
20170331 87
run;
data B;
input caldt \$ mret \$;
cards;
20170325 .
20170326 .
21070327 0.45
20170328 0.98
20170329 0.67
20170330 0.50
20170331 0.87
run;

Super User
Posts: 9,599

## Re: join two tables based on date

You should have got an error when that ran then as I had a and b the wrong way round (hence why its a good idea to always post test data in the form of a datastep so we can test):

```data A;
input caldt \$ TNA \$;
cards;
21070327 20
20170328 50
20170329 120
20170330 50
20170331 87
run;
data B;
input caldt \$ mret \$;
cards;
20170325 .
20170326 .
21070327 0.45
20170328 0.98
20170329 0.67
20170330 0.50
20170331 0.87
run;
proc sql;
create table C as
select  B.*,
A.TNA
from    A
right join B
on       A.CALDT=B.CALDT;
quit;```
Frequent Contributor
Posts: 126

## Re: join two tables based on date

it did not give the right table C. I want that the program repeat the prevous observation of TNA if it does not exist in A.

Please look carefully to the wanted table

Table C

caldt            mret   TNA

20170325     .         20

20170326     .         20

21070327    0.45     20

20170328    0.98    50

20170329    0.67    120

20170330     0.50    50

20170331     0.87    87

Frequent Contributor
Posts: 126

## Re: join two tables based on date

Sorry I make a mistake in table A.

I put aslo the table C as table to look to the result

data A;
input caldt \$ TNA \$;
cards;
20170325 20
20170326 .
21070327 .
20170328 50
20170329 120
20170330 50
20170331 87
run;
data B;
input caldt \$ mret \$;
cards;
20170325 .
20170326 .
21070327 0.45
20170328 0.98
20170329 0.67
20170330 0.50
20170331 0.87
run;
data c ;
input caldt \$ mret \$ TNA \$;
cards;
20170325 . 20
20170326 . 20
21070327 0.45 20
20170328 0.98 50
20170329 0.67 120
20170330 0.50 50
20170331 0.87 87
run;

Solution
‎04-03-2017 02:29 PM
Super User
Posts: 9,599

## Re: join two tables based on date

[ Edited ]

Edit, I noted in your test data you had 2107 year values, I assume these were typed incorrectly and fixed them.

If the new data is true, then all you have to do is a minor update to table A before the merge:

```data A;
input caldt \$ TNA \$;
cards;
20170325 20
20170326 .
20170327 .
20170328 50
20170329 120
20170330 50
20170331 87
run;
data B;
input caldt \$ mret \$;
cards;
20170325 .
20170326 .
20170327 0.45
20170328 0.98
20170329 0.67
20170330 0.50
20170331 0.87
run;
data a;
set a;
retain lstv;
if tna ne "" then lstv=tna;
if tna="" then tna=lstv;
run;

proc sql;
create table C as
select  B.*,
A.TNA
from    A
right join B
on       A.CALDT=B.CALDT;
quit;```

However, I assume here that the dates are really character, and that the first obs get copied down.  What your probably after, which is slightly different, is to take a baseline value and use that for missing elements.  In which case in your baseline data, create a flag (or logic) and pull that in and do a case when, for example if the first date was to be used for missings\;

```proc sql;
create table C as
select  B.*,
case when A.TNA is null then X.TNA else A.TNA end as TNA
from    A
right join B
on       A.CALDT=B.CALDT
left join (select TNA from A having input(CALDT,yymmdd10.)=min(input(CALDT,yymmdd10.))) X
on      1=1;
quit;```

☑ This topic is solved.