• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 188
  • Last Modified:

Please help me solve this senario in sas

I have 4 datasets
a ,b , c, d

a
there are four variables
b
there are two variables
c
there is one variable
d
there is one variable

I need to merge these datasets to get a final dataset

There is a common variable between a and b

Common variable between  b and c

So I am thinking of merging  a and b with the common variable  to get temporary dataset e

and use the common variable of b to merge e and c to get the temporary dataset n

and merge n with d using the common variable between b and d to get final dataset g

Is this process correct to see that the data is not shuffled.
0
aruku
Asked:
aruku
  • 3
1 Solution
 
bradanelsonCommented:
Depending on the desired outcome, the joins may change.  Are you wanting to keep all records in a particular dataset even if they don't match a joining dataset?  Or, are you just wanting to keep the records where they match the joining datasets?

Example of code to join all datasets, keeping all records in dataset a.
PROC SQL;
    CREATE TABLE merged AS
0
 
bradanelsonCommented:
Sorry, here is the code...

PROC SQL;
    CREATE TABLE merged AS
    SELECT a.*, b.*, c.*, d.*
    FROM a
        LEFT JOIN b
        ON a.key=b.key

        LEFT JOIN c
        ON b.id=c.id

        LEFT JOIN d
        ON b.id=d.id;
QUIT;
0
 
bradanelsonCommented:
To just keep the records that match all datasets you can do this:
PROC SQL;
    CREATE TABLE merged AS
    SELECT a.*, b.*, c.*, d.*
    FROM a
        INNER JOIN b
        ON a.key=b.key

        INNER JOIN c
        ON b.id=c.id

        INNER JOIN d
        ON b.id=d.id;
QUIT;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now