Help using Base SAS procedures

Joining numerical and alphanumerical value

Accepted Solution Solved
Reply
Regular Contributor
Posts: 186
Accepted Solution

Joining numerical and alphanumerical value

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.


Accepted Solutions
Solution
‎02-07-2013 02:46 PM
Super Contributor
Posts: 578

Re: Joining numerical and alphanumerical value

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)

View solution in original post


All Replies
Solution
‎02-07-2013 02:46 PM
Super Contributor
Posts: 578

Re: Joining numerical and alphanumerical value

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)

Respected Advisor
Posts: 3,156

Re: Joining numerical and alphanumerical value

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

Regular Contributor
Posts: 186

Re: Joining numerical and alphanumerical value

Thank you both for your replies.

Exactly what I needed.

Best regards.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 150 views
  • 3 likes
  • 3 in conversation