Here is the code run: proc sql; create table work.test as select a.Client_id, a.Client_name, a.Score, a.Status,                     (a.score-b.score) as Score_diff,                     case when a.status=b.status then 'match' else 'no match' end as Status_test from Client_Report2 a           left join Client_Report1 b on a.Client_id=input(b.Client_id,6.); quit; with suggestion from Astounding in post 25. ... View more

@Astounding the code above run with the change you made above. However the result is not what i was expecting. The original data Client_Report1 has 56965 rows and Client_Report2 has 56987 rows. The result of the work.test shows 2654809 rows which cannot be loaded. i know it is hard for you to check without data in front you. ... View more

here is the change made: proc sql; create table work.test as select a.Client_id, a.Client_name, a.Score, a.Status,                     (a.score-b.score) as Score_diff,                     case when a.status=b.status then 'match' else 'no match' end as Status_test from Client_Report2 a           left join Client_Report1 b on b.Client_id=input(a.Client_id,6.); quit; i got this error in the log:   ERROR: INPUT function requires a character argument. ERROR: INPUT function requires a character argument. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.04 seconds cpu time 0.00 seconds any idea? ... View more

yep that s correct! the correct PROC CONTENTS is above. ... View more

you are perfectly right , here is how Proc content looks like: Client_Report1:                                 TYPE          LEN    FORMAT    INFORMAT    LABEL Client_id:                  Num             8                                                  imdb id           Client_name:          CHAR            68          $68             $68            Client name Score:                     Num              8                                                 Score Status:                   CHAR             128         $128          $128          Status Client_Report2                               TYPE            LEN    FORMAT    INFORMAT    LABEL  Client_id:                 CHAR             6           $6               $6               imdb id            Client_name:          CHAR            68          $68             $68            Client name Score:                     Num              8                                                 Score Status:                   CHAR             128         $128          $128          Status Client_id data type are different. ... View more

typo error, it  should be  b.score ... View more

the file u are requesting is huge. To simplify that s what proc content show: Client_Report1:                                 TYPE          LEN    FORMAT    INFORMAT    LABEL Client_id:                  Num             6           $6               $6               imdb id            Client_name:          CHAR            68          $68             $68            Client name Score:                     Num              8                                                 Score Status:                   CHAR             128         $128          $128          Status Client_Report2                               TYPE            LEN    FORMAT    INFORMAT    LABEL  Client_id:                  Num             6           $6               $6               imdb id             Client_name:          CHAR            68          $68             $68            Client name Score:                     Num              8                                                 Score Status:                   CHAR             128         $128          $128          Status log:   ERROR: Expression using equals (=) has components that are of different data types. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.04 seconds cpu time 0.01 seconds ... View more

that s correct! do you think the error has something to do with the fact that the same variable(Status) has mismatch type like this? : 50 >60 more than a year 8% 3y ... View more

sorry mistype i did in my code as you mentioned above. ... View more

it shows: Client_id: Num Client_name: CHAR Score: Num Status: CHAR thanks! ... View more

both reports show Status as CHAR. ... View more

the variable Status is the one causing the problem because it has mixed values such as : 50 >60 more than a year 8% 3y thanks! ... View more

i did : select put(a.status,1.), put(b.status,1.)  or in the case when statement  i get this error:   ERROR: Expression using equals (=) has components that are of different data types. ERROR: Numeric format F in PUT function requires a numeric argument. ERROR: Numeric format F in PUT function requires a numeric argument. any idea? thanks! ... View more

what do u mean by "equal two sides"? thanks! ... View more