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.
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)
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)
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
Thank you both for your replies.
Exactly what I needed.
Best regards.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.