topic Data modelling in sas in SAS Data Management

Data modelling in sas

jmehul — Mon, 26 Mar 2018 11:09:42 GMT

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.

Re: Data modelling in sas

Kurt_Bremser — Mon, 26 Mar 2018 11:11:42 GMT

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

For lookups, consider creating custom formats.

Re: Data modelling in sas

LinusH — Mon, 26 Mar 2018 16:30:06 GMT

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.

Re: Data modelling in sas

jmehul — Mon, 26 Mar 2018 17:03:09 GMT

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!

Re: Data modelling in sas

Kurt_Bremser — Mon, 26 Mar 2018 17:22:26 GMT

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

Re: Data modelling in sas

LinusH — Mon, 26 Mar 2018 17:58:29 GMT

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?

Re: Data modelling in sas

LinusH — Mon, 26 Mar 2018 18:04:44 GMT

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