BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
elwayfan446
Barite | Level 11

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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

 

View solution in original post

5 REPLIES 5
novinosrin
Tourmaline | Level 20

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;
elwayfan446
Barite | Level 11

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:

 

elwayfan446_0-1592419046480.png

 

novinosrin
Tourmaline | Level 20

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

 

elwayfan446
Barite | Level 11

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

Tom
Super User Tom
Super User

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) 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 821 views
  • 3 likes
  • 3 in conversation