topic Re: select from more than one dataset in SAS Programming

select from more than one dataset

HeatherNewton — Wed, 28 Dec 2022 15:30:25 GMT

create table pd_pool_id_mob_le_6_l0_pre as
     select
           a.account_no
           a.level4,
           a.level3
from
           monthly.pd_pool_id_&&yymmdd_L0 a,
           staging.credit_card_account_h_&&yymmdd_L0 b
where
           a.account_no=b.account_no and
           intck('month', date_card_opened, "&SAMPDATE"d)+1<=7;

what does it mean to select from more than one dataset?

is this an inner join?

Re: select from more than one dataset

PaigeMiller — Wed, 28 Dec 2022 15:37:37 GMT

In this case, the code is performing a Cartesian join of the two data sets. Examples and further explanation here: https://sasnrd.com/sas-cartesian-product-proc-sql-data-step/

Re: select from more than one dataset

Tom — Wed, 28 Dec 2022 16:05:57 GMT

You are JOINing the two datasets. (kind of like a MERGE in normal SAS code, but more complicated).

In this case since you are testing if the account numbers match between the two datasets it is like an INNER JOIN.

So you are taking three variables from the "monthly" dataset, but only for the account numbers that are also in the "staging" dataset. You are also filtering on the DATE_CARD_OPENED variable, but the code does not tell us which of two datasets has that variable. (if both have the variable the code will fail for being ambiguous).

Re: select from more than one dataset

gema — Mon, 09 Jan 2023 18:30:17 GMT

you are really only selecting from first dataset if account_no on is in both data sets (inner join) and date criteria is met