topic Re: join two tables based on date in SAS Procedures

join two tables based on date

sasphd — Mon, 03 Apr 2017 13:26:12 GMT

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

Re: join two tables based on date

RW9 — Mon, 03 Apr 2017 13:31:20 GMT

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;

Re: join two tables based on date

sasphd — Mon, 03 Apr 2017 14:08:52 GMT

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

Re: join two tables based on date

RW9 — Mon, 03 Apr 2017 15:29:23 GMT

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.

Re: join two tables based on date

sasphd — Mon, 03 Apr 2017 15:44:47 GMT

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;

Re: join two tables based on date

RW9 — Mon, 03 Apr 2017 16:49:51 GMT

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;

Re: join two tables based on date

sasphd — Mon, 03 Apr 2017 17:06:32 GMT

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

Re: join two tables based on date

sasphd — Mon, 03 Apr 2017 17:18:12 GMT

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;

Re: join two tables based on date

RW9 — Mon, 03 Apr 2017 17:33:40 GMT

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;