Append two tables in SAS Visual Analytics

[Ankita_]

Hi,

I have two datasets in my dashboard Data A and Data B -

Data A -

Month      Qty

Jan22         1

Feb22        2

Mar22        3

Apr22        4

May22       5

Data B-

Month      Qty

Jun22        1

Jul22         2

Aug22       3

Sep22       4

Oct22        5

I'm looking to create a Data C which appends Data A and B and gives this output-

Month      Qty

Jan22         1

Feb22        2

Mar22        3

Apr22        4

May22       5

Jun22        1

Jul22         2

Aug22       3

Sep22       4

Oct22        5

[Sam_SAS]

Hello, I think what you want is a join:
https://documentation.sas.com/doc/en/vacdc/v_018/vareportdata/n167hzcxe4pi2yn1b50jv5ujgtor.htm

Is that what you are looking for?

Thanks,
Sam

[Sam_SAS]

On second thought, I think you would be better off concatenating these tables in your data preparation, in SAS Data Studio:
https://documentation.sas.com/doc/en/dprepcdc/v_008/datastudioadv/n1rxa266feelusn1xl9ynf3xoy2w.htm

You need to have certain credentials to do this.

[Ankita_]

Yes, I'm looking to append. But I'm unable to find this option in SAS Visual Analytics. Can you please help with that?

[Sam_SAS]

Directly in VA, you can only do a join. To do an append, you need to use a different SAS webapp.

If you open the application selector menu at the top left, do you see "Prepare Data"?

[Ankita_]

No, I see Explore and Visualize, Build models, Share and collaborate, develop SAS code. And some admin options - build custom graphs, explore lineage, manage environment.

[Sam_SAS]

@Madelyn_SAS @HunterT_SAS can you help with this appending data question? I think they should be able to use SAS code to append the two tables but I am not up to speed on how you do this within CAS.

[HunterT_SAS]

As @Sam_SAS mentioned, there is no way to append tables within Visual Analytics itself. If you had access to SAS Data Studio (the Prepare Data application referenced earlier), it can easily be done there. Otherwise you could do it with SAS code (I am not familiar with how this could be done either), or any other way you might be comfortable with already.

I think for the purposes of this thread, the answer is NO it cannot be done in Visual Analytics, and would need to be accomplished outside of VA with any number of methods. 

[Ankita_]

Got it!. Can you also help with moving data from CAS to SAS to perform such operations in data studio?

[Madelyn_SAS]

At this point, I would suggest posting to the SAS Programming community. 

Also, for reference, this is the Programming Documentation for the SAS Viya Platform

[acordes · Accepted solution]

As mentioned already by @HunterT_SAS and @Sam_SAS , this should be done in a data preprocessing step- 

Nevertheless, IT IS possible to do an append operation in Visual Analytics. 

It's quite forced, but you can achieve it by full joining on something impossible to match (to keep ALL observations from both tables).

Then you should unify the duplicated field names. 

[Madelyn_SAS]

Before attempting to join, you should review the considerations for using data joins; 

https://go.documentation.sas.com/doc/en/vacdc/v_018/vareportdata/n167hzcxe4pi2yn1b50jv5ujgtor.htm#p0... 

[Sam_SAS]

As Madelyn notes, there are some drawbacks (chiefly performance) to performing the join in VA rather than joining/appending the tables as part of your data preparation.

You can use the CAS LIBNAME engine to append the tables (example), or if you have the Viya 4 platform you also can use the PROC CASUTIL APPEND statement. I am not certain this is good practice in a production environment. If you have a data administrator, it would probably be preferable to have that person append the data for you.

Alternatively, if the data is small and you have the appropriate permissions, you could append the tables locally on your machine and import the result as a new table.

For help using these programming approaches, you should create a thread either in the main SAS Viya community or in the SAS Programming community.

[Kemal_Sozer]

Appending table is not available on VA. Create and prepare data before VA process and after this send it to the memory. Use SAS Studio to append and send data to the related CAS library. Latest version of VA, supports joins but this is not your looking for in my opinion.

Best,

Kemal.