Date:         Wed, 29 Jul 2009 13:05:26 -0700
Reply-To:     billyk43 <keith.andersen@COMCAST.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         billyk43 <keith.andersen@COMCAST.NET>
Organization: http://groups.google.com
Subject:      Re: Joining tables problem
Comments: To: sas-l@uga.edu
Content-Type: text/plain; charset=ISO-8859-1

On Jul 29, 6:22 am, sickpur...@GMAIL.COM (Mark Marquez) wrote:
> hi all,
>
> i have a simple problem in joining tables. my code is this:
>
> PROC SQL;
>       CREATE TABLE WORK._BIL_TERM_LOAN AS
>       SELECT
>         A.SAS_SOC_CDE,
>         A.DUE_DTE,
>         CASE
>           WHEN B.NEW_REF_NO NE "" THEN
>             B.NEW_REF_NO
>           ELSE
>             A.REF_NO
>           END AS REF_NO,
>         A.ACC_NO,
>         A.ACC_NO_INTERNAL,
>         A.FCYFLUCT,
>         A.FCYBRTYP,
>         A.INTREVED,
>         A.ACC_PRINCIPAL_PAID_AMOUNT,
>         A.REPAYMENT_TERM,
>         A.USANCE_DAY,
>         A.LOFCBALN,
>         A.LOFBBALN,
>         A.CCY_CDE,
>         A.LOHKCUSL,
>         A.LOHKBANL,
>         A.ACCINT_FL,
>         A.ACCINT_FL_CUST,
>         A.SUSINT_FL,
>         A.INTERATE,
>         A.REPAYMTD,
>         A.NOFINSTL,
>         A.REPAYPER,
>         A.REPAYAMT,
>         A.INT_RATE_SIGN,
>         A.FCYRLOAD,
>         A.RECORD_STATUS,
>         A.EUSANCEF,
>         A.CREATE_DATE,
>         A.HIBOR_RATE_SIGN,
>         A.FCYRLOAD_HIBOR,
>         A.GOV_GUAR_RATIO,
>         A.REF_NO AS OLD_ACC_NO,
>         A.LAST_UPDTE_DTE
>       FROM HDATA.BIL_TERM_LOAN A
>       LEFT JOIN MAP.BLS_MAPPING B
>         ON
>           A.REF_NO = B.OLD_REF_NO
>       ;
>     QUIT;
>
> now i want the code to join tables even if 2 or more variables are missing
> as long ast its not the joining key (REF_NO). pls help!!!

I don't believe you can do that with PROC SQL as it expects all of the
fields specified in the Select statement to actually exist in the
source data (though I could be wrong).

However, you can use a SAS data step to get the job done.

e.g. something like this:

DATA WORK._BIL_TERM_LOAN;
  <LENGTH statement here to specify your output fields>;
  MERGE HDATA.BIL_TERM_LOAN (in=inA)
               MAP.BLS_MAPPING (in=inB rename=(OLD_REF_NO=REF_NO));
  BY REF_NO;
  IF inA;
  <KEEP statement here to retain your output fields>;
RUN;

Hope that helps....