CODEWITHSUNDEEP
sqlsasproc
HarryL
HarryL

Reputation: 1

PROC SQL How to join two tables with no empty cells and plus a condition?

I'm learning SQL in SAS and I was requested to join two tables with no missing information and to include a condition within the Join statement. I've tried several types of Join, and Full Join ended up to be the closest result that I need. But yet, I couldn't figure it out how to remove rows with empty values neither how to keep only the Sessions that I needed. I can only use PROC SQL.

FICTIONAL DATA:

PROC SQL;
    CREATE TABLE Students_SCORE AS
    SELECT *
    FROM TABLE_A AS A
        FULL JOIN (SELECT *
        FROM TABLE_B
        WHERE SESSION_ID HAVING ('2021' AND '2022')) AS B
            ON A.ID=B_ID 
            AND A.NAME=B_NAME;
QUIT;

The table received that sums up all the rows, but show empty cells in either side (empty.A.rows with filled.B.rows or filled.A.rows with empty.B.rows). Please note that the example is fictional, so if there is a way to not mention all the columns would be great as the dataset that I have there are 15 columns from each table, 4 in common. Thanks for the help!

Tables

Result

Upvotes: 0

Views: 398

Answers (2)

HarryL
HarryL

Reputation: 1

I was able to resolve using this:

proc sql;
    create table Want as
    select tableA.*, tableB.*  
    from tableA
    full join (select *
    from tableB
    where session between 2021 and 2022) tableB
    on tableA.id = tableB.id;
quit;

Upvotes: 0

Kermit
Kermit

Reputation: 3117

You just need a simple inner join.

proc sql;
    create table want as
    select tableA.*, tableB.scoreengl, tableB.scorescien, tableB.session  
    from tableA
    inner join tableB
    on tableA.id = tableB.id
    ;
quit;

id   name  scoreMATH  scoreHIST  scoreENGL  scoreSCIEN  session
 1  David     45         59.5       77.5       60        2021
 3  Sam       45         45         65         52        2022
 4  Ram       54         60         80         75        2022
 5  Bart      87         88.5       90         98        2021
 6  Mary      92         92         95         92        2021
 8  Dane      23         50         55         45        2021
 9  Jenny     87         92         98         80        2022
10  Ken       87         60         85         88        2021

Edit following comment.

proc sql;
    create table want as
    select tableA.*, tableB.scoreengl, tableB.scorescien, tableB.session  
    from tableA
    inner join tableB
    on tableA.id = tableB.id
    where session in ('2021', '2022')
    ;
quit;

Upvotes: 0

Related Questions