SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Data modelling in sas

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

Data modelling in sas

I want to link 2 or more tables. 
One way is to join the tables which will increase the size of my fact tables

Is there any other way to link 2 or more table?

Can we built a data model using any SAS product.


Accepted Solutions
Solution
‎03-26-2018 07:13 AM
Super User
Posts: 9,890

Re: Data modelling in sas

You can create views that do the merge on the fly when accessed.

For lookups, consider creating custom formats.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Solution
‎03-26-2018 07:13 AM
Super User
Posts: 9,890

Re: Data modelling in sas

You can create views that do the merge on the fly when accessed.

For lookups, consider creating custom formats.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 5,851

Re: Data modelling in sas

SAS doesn't have any data modelling themselves.
But you can pretty much implement any data model in SAS.
I see that you marked this as solved, but at least I am not able to understand your requirements and constraints, and specifically how you are going to solve this.
It would be interesting to understand what reporting requirements you have, and what type of queries/tools you are you using.
Data never sleeps
Occasional Contributor
Posts: 18

Re: Data modelling in sas

I want to link my facts and dimension table and I don't want to join them as it will increase the size of my fact table.
As he said I have to create views at the rum time what I was thinking of using the proc sql statement.

load fact (A) and dimension (B) tables and then 


proc sql;

select  A.age,

           B.salary,

from A,B

where A.Id= B.Id;
Quit;

 

then I will drop the table A and B.

 

 

Need Suggestions!

Super User
Posts: 9,890

Re: Data modelling in sas

Depending on table sizes, you can create a format from one table and use it in the other.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 5,851

Re: Data modelling in sas

Not sure if views are the best alternative, you need to test performance with example queries.
Why do you need to create a layer on top? Can't the application/users do the star schema joins?
Data never sleeps
Super User
Posts: 5,851

Re: Data modelling in sas

Addition:
1. Consider the star schema as the master for the report layer. The star schema will help you to keep the data model consistent.
2. A semantic demoralized layer should be derived from and potentially have the ability to be totally recreated from the star schema (if physical).
3. You can't delete the underlying tables when you create a view. If you think that's feasable I think you need on site design assistance....
Data never sleeps
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 143 views
  • 1 like
  • 3 in conversation