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?
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
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;
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:
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
Thank you for the help. I didn't realize that was the issue.
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)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.