proc sql - creating a table by left joining two datasets

[jeremy4]

Hi,

I would like to use proc sql to create a table and do a left join between two datasets - I have the following two lines to start off with so far:

proc sql;

create table NEV_TIME_ON_BOOK as

Steps to be completed:

1. Variables to select from datasets A and B:

Dataset cdpview.card_2019 as A - select:

account_ID, month_end, time_on_book

Dataset cdpview.output_2019 as B - select:

account_ID, month_end, portfolio_segment

2. Left join

I would like to do a left join:

cdpview.card_2019 as A
left join cdpview.output_2019 as B
on a.account_id = B.account_id and a.mth_end = b.mth_end

3. Only see results where portfolio_segment='NEV'

Also, from dataset B (cdpview.output_2019), I would only want to see results where portfolio_segment='NEV'.

Is there code so that once the left join has been done, the table NEV_TIME_ON_BOOK:

1. Lists the 'time_on_book' results for each of the records where portfolio_segment='NEV'.

2. The table is ordered by account_ID.

Thanks in advance!

[gamotte · Accepted solution]

Hello,

You have given all the elements. Putting them together gives :

proc sql;
    create table NEV_TIME_ON_BOOK as
    select a.account_ID, a.month_end, a.time_on_book, b.portfolio_segment
    from cdpview.card_2019 as a
    left join cdpview.output_2019 as b
    on a.account_id = b.account_id and a.month_end = b.month_end
    where b.portfolio_segment='NEV'
    order by a.account_ID
    ;
quit;

I assumed mth_end was a typo for month_end.