How to append CAS tables with different columns

[ElisabettaC]

Hello,
I need to append two tables loaded in SAS:
- STORIC_TABLE of about 1.2 TB and 50 columns
- TAB1 of about 30 GB and 51 columns (one column more than the STORIC_TABLE)
I would like to know which is the most optimized code to append the TAB1 to the STORIC_TABLE. 

Obviously, due to the presence of one more field, the STORIC_TABLE will have missing values corresponding the new TAB1 field
thanks
ElisabettaC

[tarheel13]

You could just concatenate them.

Data stacked;
Set tab1 storic_table;
Run;

[ElisabettaC]

Hi lrackley,
thanks for your quick reply.
I tried this solution but it's not very efficient because it rewrites the first table which is too big (about 1.2 tb).
Any other suggestions without rewriting tables?

[tarheel13]

Could try proc SQL outer union or proc append. This paper shows all the way to combine data vertically and you could read more about the efficiency as well as see examples. 

https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2019/3161-2019.pdf 

[ElisabettaC]

The problem is that proc sql with create table statement and proc append don't work with SAS Viya CAS tables.
Suggestions on how to use OUTER UNION CORR in PROC FEDSQL?

[tarheel13]

Haha I actually don’t know what CAS is. I don’t use Viya! I found this documentation. It looks like union is supported in FedSQL.
https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=fedsqlref&docsetTarget=n1...

[ElisabettaC]

Unfortunately, the outer union corr option doesn't work with proc fedsql, as shown in the image below:

[tarheel13]

The link I sent you said "corresponding". did you try it with that? 

[ElisabettaC]

"Union corresponding" doesn't fail but doesn't return the expected result!
With union corresponding  it appends only the fields that match (50 fields) and doesn't create the new field.

The expected table must have 51 columns.

[tarheel13]

Why do you have to use corresponding? What if you just try "outer union"? Do you get 51 then? 

[ElisabettaC]

No, I get a syntax error (same error even with outer union corr)