How to join several tables if certain tables do not exist?

[yangtaotai]

Hello everyone:

I have a question of joining data tables. Suppose my code will produce three tables A, B, and C and I want to join these three tables together. However, these three tables do not always exist and if  this happens, I just want to join the tables with data. For example, if table A does not exist then join B and C tables together.

I know that if you create a table with all columns but no data in it, you can still join it without error. However, my A, B, C tables are created from a macro and when the empty table will not have any columns.

Therefore, I want to know what is the best practice to handle this situations?

Thank you in advance,

Tao

[RW9 · Accepted solution]

Well, you could conditionally execute parts of the code based on existence of the dataset name in sashelp.vtable.  However, my suggestion would be to create the three tables up front and have your macro append data to them.  That way you know they are always present.  Also important, if some data is not created, does that not mean your logic would fall over, i.e. if you are not getting what you expect, maybe re-think the original idea.  I.e. always know your data, and your process - this is more important than doing some coding.

[yangtaotai]

Hi RW9,

Thank you for your reply!   I wonder if I literally create a table with columns but no data in it and then my macro append A, B, and C to this temp table and this will guarantee the temp table will always have all the existing records. Not sure if this is what you are describing in your reply.

Tao

[RW9]

Yes, indeed that is my point.  Just simply create empty tables:

proc sql;

     create table A (id num,avar num);

     create table B (id num,avar num);

     create table C (id num,avar num);

quit;

Then in your macro, rather than creating a dataset, just do:

data a;

     set a some_other data;

run;