[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Please help me solve this senario in sas

Posted on 2009-12-23
3
Medium Priority
?
182 Views
Last Modified: 2013-11-16
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
Comment
Question by:aruku
  • 3
3 Comments
 
LVL 9

Expert Comment

by:bradanelson
ID: 26113989
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
 
LVL 9

Expert Comment

by:bradanelson
ID: 26114013
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
 
LVL 9

Accepted Solution

by:
bradanelson earned 2000 total points
ID: 26114025
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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question