topic Re: How to append CAS tables with different columns in SAS Programming

How to append CAS tables with different columns

ElisabettaC — Mon, 18 Jan 2021 10:12:36 GMT

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

Re: How to append CAS tables with different columns

tarheel13 — Mon, 18 Jan 2021 11:26:49 GMT

You could just concatenate them.

Data stacked;
Set tab1 storic_table;
Run;

Re: How to append CAS tables with different columns

ElisabettaC — Mon, 18 Jan 2021 11:36:14 GMT

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?

Re: How to append CAS tables with different columns

tarheel13 — Mon, 18 Jan 2021 12:00:06 GMT

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

Re: How to append CAS tables with different columns

ElisabettaC — Mon, 18 Jan 2021 12:35:12 GMT

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?

Re: How to append CAS tables with different columns

tarheel13 — Mon, 18 Jan 2021 12:50:45 GMT

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=n1s9tikr56z85cn11n5hou1gzj2v.htm&locale=en

Re: How to append CAS tables with different columns

ElisabettaC — Mon, 18 Jan 2021 13:54:28 GMT

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

Re: How to append CAS tables with different columns

tarheel13 — Mon, 18 Jan 2021 14:05:21 GMT

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

Re: How to append CAS tables with different columns

ElisabettaC — Mon, 18 Jan 2021 14:26:14 GMT

"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.

Re: How to append CAS tables with different columns

tarheel13 — Mon, 18 Jan 2021 14:32:36 GMT

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

Re: How to append CAS tables with different columns

ElisabettaC — Mon, 18 Jan 2021 14:44:10 GMT

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