topic Re: Joining numerical and alphanumerical value in SAS Procedures

Joining numerical and alphanumerical value

nicnad — Thu, 07 Feb 2013 19:32:18 GMT

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.

Re: Joining numerical and alphanumerical value

DBailey — Thu, 07 Feb 2013 19:46:25 GMT

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)

Re: Joining numerical and alphanumerical value

Haikuo — Thu, 07 Feb 2013 19:58:47 GMT

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

Re: Joining numerical and alphanumerical value

nicnad — Thu, 07 Feb 2013 20:09:30 GMT

Thank you both for your replies.

Exactly what I needed.

Best regards.