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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.