BookmarkSubscribeRSS Feed

How to Join on Multiple Keys in a CAS View

Started ‎07-18-2021 by
Modified ‎07-18-2021 by
Views 4,098

The join functionality in a CAS View has been extended. You can now join tables on multiple keys since the SAS Viya 2020.1.3 stable cadence. Therefore, the changes are included in the 2021.1 long term support.

 

If you are not familiar with the star schemas in CAS, I recommend reviewing the following posts:

 

 

Before SAS Viya 2020.1.3

 

bt_1_CAS-STAR-SCHEMA-Data-Model.png

Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.

 

After SAS Viya 2020.1.3

bt_2_CAS-STAR-SCHEMA-Data-Model-Multiple-Keys.png

 

Code

You can now specify multiple join keys. For example, the customer code and the record source:

 

{keys={'m_customer_code = c_customer_code', 'm_record_source = c_record_source'}

 

To create a CAS View (the star schema in the second diagram), you could write:

 

cas mySession sessopts=(caslib="casuser" timeout=1800 locale="en_US" 
	metrics="true" dqLocale="(ENUSA)");
caslib _all_ assign;
* Multi-key join FACT + DIM 2 keys + DIM one key;
proc casutil;
droptable casdata="mail_view_multi" incaslib="casuser" quiet;
quit;
proc cas;
table.view / promote=true name='mail_view_multi'
tables={
{name='mailorder_mkey', varlist={'qty'}, as='m'},
{keys={'m_customer_code = c_customer_code', 'm_record_source = c_record_source'},
name='customers_mkey',
varList={'addr1','addr2','city',
'region','state','zip'}, as='c'},
{keys={'m_pcode = p_pcode'},
name='products_rep',  varlist={'price', 'cost', 'descrip', 'type'},
computedVars={{name="price_cost_ratio"}}, 
computedVarsProgram="price_cost_ratio = price/cost;", as='p'},
{keys={'m_catcode = cat_catcode'},
name='catcode_rep', varlist={'catalog'}, as='cat'}
};
quit;
cas mySession terminate;

Note how the price cost ratio is defined as a calculation within the view.

 

Pre-requisites

Facts and dimensions tables loaded in CAS.

 

Acknowledgements

Special thanks to Gordon Keener in SAS R&D for his time, efforts, and sharing information on this topic.

 

References

I would recommend the following resources:

 

 

Thank you for your time reading this post. Please share your experience with the CAS Star Schemas and help others.

 

If you wish to get more information, please leave a comment.

 

Find more articles from SAS Global Enablement and Learning here.

Version history
Last update:
‎07-18-2021 10:22 PM
Updated by:
Contributors

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

SAS AI and Machine Learning Courses

The rapid growth of AI technologies is driving an AI skills gap and demand for AI talent. Ready to grow your AI literacy? SAS offers free ways to get started for beginners, business leaders, and analytics professionals of all skill levels. Your future self will thank you.

Get started