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
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;
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;
Your program did not give me the right table C. It create a lot of "."
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.
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;
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;
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
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;
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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.