Joining numerical and alphanumerical value

[nicnad]

Hi,

I am trying to make a inner join in sql on two variable of different type, where no_client_rdm_sid is numerical and entity_key is alphanumerical

PROC SQL;
CREATE TABLE icoplib.customer_comments
  AS SELECT 

  absentnorkom.NO_CLIENT_RDM_SID,
  CUSTOMER_ACTION_LOG_NUM.COMMENT_TEXT

FROM ICOPLIB.ABSENTNORKOM AS ABSENTNORKOM
   LEFT join icoplib.CUSTOMER_ACTION_LOG_NUM AS CUSTOMER_ACTION_LOG_NUM ON ( absentnorkom.NO_CLIENT_RDM_SID = CUSTOMER_ACTION_LOG.entity_key)

Is there a way to transform the variable within the sql query or do I really need to use an input statement like the following? :

data icoplib.customer_action_log_num;

set nksmprd.customer_action_log;

entity_key_num = input(entity_key, 20.);

run;

If I need to use this input statement, how do I make sure that it only transform numerical value (i,e limit the input to numerical value only), since my entity_key records also contains alphanumerical value and I don't want to trigger an error each time it analyze an alphanumerical value.

Thank you for your help and time.

[DBailey · Accepted solution]

PROC SQL;
CREATE TABLE icoplib.customer_comments
  AS SELECT 

  absentnorkom.NO_CLIENT_RDM_SID,
  CUSTOMER_ACTION_LOG_NUM.COMMENT_TEXT

FROM ICOPLIB.ABSENTNORKOM AS ABSENTNORKOM
   LEFT join icoplib.CUSTOMER_ACTION_LOG_NUM AS CUSTOMER_ACTION_LOG_NUM

ON  absentnorkom.NO_CLIENT_RDM_SID = input(CUSTOMER_ACTION_LOG.entity_key,20)

[Haikuo]

First of all, there are only TWO types of variables in SAS table, Number or Char. The one you referred as alphanumeric is Char.

Second, in this case, you better off convert Number to Char instead of the other way around to avoiding those error messages:

ON put(absentnorkom.NO_CLIENT_RDM_SID,best20.) = CUSTOMER_ACTION_LOG.entity_key

Also, you probably need to adjust the format to "best20.x" to accommodate possible decimal point or apply upcase(), left() etc to make sure you get what you want.

Haikuo

[nicnad]

Thank you both for your replies.

Exactly what I needed.

Best regards.