topic Re: Using KEEP with Joins? in SAS Programming

Using KEEP with Joins?

elwayfan446 — Wed, 17 Jun 2020 18:12:03 GMT

Is it possible to use the KEEP= statement with joined datasets? I have tried the following and I am getting errors.

select *
from table_a
left join table_b (KEEP= id name phone email ) on table_b.id = table_a.id
;
quit;

I have also tried to put the join criteria in front of the KEEP= statement...

select *
from table_a
left join table_b on table_b.id = table_a.id (KEEP= id name phone email ) 
;
quit;

I am getting errors in both of these. Is doing this even possible?

Re: Using KEEP with Joins?

novinosrin — Wed, 17 Jun 2020 18:18:50 GMT

Hi @elwayfan446 Can you post the log please, and particularly the errors that you got for this one-

select *
from table_a
left join table_b (KEEP= id name phone email ) 
on table_b.id = table_a.id
;
quit;

Re: Using KEEP with Joins?

elwayfan446 — Wed, 17 Jun 2020 18:38:23 GMT

Thanks @novinosrin.

Let me give you the actual join. My code above was an example.

select *
from dwacq.msr_acquired_data_key_xref xref 
	left join abdeods.ods_bde_loan l (keep= loan_number first_principal_balance net_payment_due_date first_p_and_i_amount total_escrow_due_amount total_payment_due_amount loan_term remaining_term annual_interest_rate payment_in_full_date lo_type record_status updated_date) on l.loan_number = xref.xref_pnc_loan_number 
	left join abdeods.ods_bde_servicing_fee sf (keep= loan_number investor_loan_number updated_date) on sf.loan_number = xref.xref_pnc_loan_number
	left join abdeods.ods_bde_original_loan ol (keep= loan_number acquisition_date acquisition_or_sale_identifier acquired_principal_balance loan_closing_date first_due_date orig_loan_to_value_ratio loan_purpose_code updated_date) on ol.loan_number = xref.xref_pnc_loan_number
	left join abdeods.ods_bde_user_defined ud (keep= loan_number user_02_position_field_2b user_03_position_field_6a user_04_position_field_9a user_04_position_field_8a user_05_position_field_4b user_10_position_field_6b user_35_position_field_2a user_50_position_field_1a user_04_position_field_5b updated_date) on ud.loan_number = xref.xref_pnc_loan_number
	left join abdeods.ods_bde_property p (keep= loan_number property_alpha_state_code property_zip_code updated_date) on p.loan_number = xref.xref_pnc_loan_number
	left join dwacq.msr_acquired_loanpurpose_desc lpd on lpd.loan_purp_code = ol.loan_purpose_code
	left join dwacq.msr_acquired_lotype_desc lod on lod.lo_type_code = l.lo_type

Here is the log error I am getting:

Re: Using KEEP with Joins?

novinosrin — Wed, 17 Jun 2020 18:43:40 GMT

This error is clear

left join abdeods.ods_bde_loan l (keep= loan_number first_principal_balance net_payment_due_date first_p_and_i_amount total_escrow_due_amount total_payment_due_amount loan_term remaining_term annual_interest_rate payment_in_full_date lo_type record_status updated_date) on l.loan_number = xref.xref_pnc_loan_number

should be

left join abdeods.ods_bde_loan (keep= loan_number first_principal_balance net_payment_due_date first_p_and_i_amount total_escrow_due_amount total_payment_due_amount loan_term remaining_term annual_interest_rate payment_in_full_date lo_type record_status updated_date) l  on l.loan_number = xref.xref_pnc_loan_number

The dataset option (keep=) should precede the alias name l

Re: Using KEEP with Joins?

Tom — Wed, 17 Jun 2020 19:42:44 GMT

If you get in the habit of putting the dataset options touching the dataset name it will be harder for you to insert random text between them.

table_b(KEEP=id name phone email)

Re: Using KEEP with Joins?

elwayfan446 — Thu, 18 Jun 2020 13:45:15 GMT

Thank you for the help. I didn't realize that was the issue.