join two tables based on date

[sasphd]

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

[RW9]

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;

[sasphd]

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

[RW9]

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.

[sasphd]

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;

[RW9]

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;

[sasphd]

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

[sasphd]

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;

[RW9 · Accepted solution]

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;