In the query below I want to attach the country code to the cell number based on MKT_ID_Cd.
But the output doesnt do that.i just gives out the phone number,
Say the iso_ph_cd is 52 (country mexico) for mkt _id_cd 002 then the cell_nbr must be 527714561241 , but what i see is only 7714561241
proc sql;
create table cell_phone as
select
a.GALC_ACCT_NO as acct_no
,CATX(d.iso_ph_cd, compress(a.GALC_ALRT_PHONE_NO,,'KD') as cell_nbr
from outdata.DMGADR_171212 a
join (
select
b.GALC_ACCT_NO
,max(GALC_LST_UPDT_TS) as last_updt_dt
from outdata.DMGADR_171212 b
where coalescec(b.GALC_ADDR_TYPE_CD,'')='PHN'
and b.GALC_ALRT_PHONE_NO ne ''
and input(compress(b.GALC_ALRT_PHONE_NO,,'KD'),10.) > 0
group by b.GALC_ACCT_NO
) b on a.GALC_ACCT_NO=b.GALC_ACCT_NO and a.GALC_LST_UPDT_TS=b.last_updt_dt
left join outdata1.isocodes d on a.mkt_id_cd=d.mkt_id_cd
where coalescec(a.GALC_ADDR_TYPE_CD)='PHN'
and a.GALC_ALRT_PHONE_NO is not null
and input(compress(a.GALC_ALRT_PHONE_NO,,'KD'),10.) > 0
;
quit;
Looks like you are missing a right parenthesis, should that be:
'KD'))
at the end of CATX??
I added the paranthesis.But still dont get
I think you're looking for CATS rather than CATX.
In CATX, the first item in the list is a delimiter to use between the remaining set of items. But your remaining set of items has only one item listed, and so doesn't need a delimiter.
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 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.