BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasphd
Lapis Lazuli | Level 10

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

 

View solution in original post

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Lapis Lazuli | Level 10

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Lapis Lazuli | Level 10

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Lapis Lazuli | Level 10

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
Lapis Lazuli | Level 10

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
Diamond | Level 26 RW9
Diamond | Level 26

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: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 2571 views
  • 1 like
  • 2 in conversation