BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Q1983
Lapis Lazuli | Level 10

Proc SQL stimer;

Connect to DB2 (&applogin);

Create table TRN_FACT as

(Select distinct *,put(PST50_ACCT_NUM,z17.) as pst50_acct_nbr

 

from Connection to DB2

(SELECT distinct PST50_ACCT_NUM,

PST50_POST_DT,

PST50_POST_SEQ,

PST50_TRAN_CD_EX,

PST50_AMT,

pst50_DB_CR_IND,

DEP_ACCT_ID

FROM REGE.V_FT_PST50_TRN_FACT

/*where PST50_AMT = 100*/

where (PST50_POST_DT BETWEEN &beg_mth6. and &Last_Mth.)/*6 month period*/

 

with ur));

Disconnect from DB2;

Quit;

 

Proc SQL;

Connect to DB2 (&applogin);

Create table t_sess as

(Select distinct *

from Connection to DB2

(Select distinct ACCT_NBR, /*as sess_acct_nbr,/*char17/*get the count for the total opened*/

/*BAL_VAL_PNT_CNT,*/

 

ACCT_OPN_AMT,

CST_CTR_CD,

CUST_FULL_NM,

EMP_NBR,

OPN_BAL_DT,

SESS_DT,

PROD_CD,

LOB_CD,

ROLE_CD,

ROLE_NM,

SEQ_NBR,

UNT_VAL_PNT_CNT,

bi_as_of_dt

from RAZ_SRC.T_SESS_DTL

where (ACCT_OPN_AMT = 100)

and (OPN_BAL_DT between &beg_mth6. and &Last_Mth.)

and (SESS_DT between &beg_mth6. and &end_mth3.)

and (ACCT_OPN_AMT NOT IN (0))

 

/*and EMP_NBR = '200545'*/

/*and (UNT_VAL_PNT_CNT +BAL_VAL_PNT_CNT > 0 )*/

with ur));

Disconnect from DB2;

Quit;

 

I want to do an inner join from the TRN_FACT table PST50_ACCT_NUM to the ACCT_NBR in the T_SESS table.  At issue is that the PST50_ACCT_NUM is numeric and the ACCT_NBR is char.  Currently I bring in both datasets separately however both are quite huge and I get timeout errors.

In the TRN_FACT table I defined the PST50_ACCT_NUM as a character.(put(PST50_ACCT_NUM,z17.) as pst50_acct_nbr)

Is there a way to define and use it to join the T_Sess table within the same pass through routine.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Krueger
Pyrite | Level 9

Since your using SQL Passthrough convert it within the passthrough and do the join here. There is the CAST() and CONVERT() functions so you can modify whichever field you choose. If you have example's of the fields it would be easier to show you.

 

 

So you would basically do:

join b on a.charfield = cast(b.numericvar as varchar(17));

View solution in original post

1 REPLY 1
Krueger
Pyrite | Level 9

Since your using SQL Passthrough convert it within the passthrough and do the join here. There is the CAST() and CONVERT() functions so you can modify whichever field you choose. If you have example's of the fields it would be easier to show you.

 

 

So you would basically do:

join b on a.charfield = cast(b.numericvar as varchar(17));

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 511 views
  • 0 likes
  • 2 in conversation