Catx

[SASPhile]

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;

[Astounding]

Looks like you are missing a right parenthesis, should that be:

'KD'))

at the end of CATX??

[SASPhile]

I added the paranthesis.But still dont get

[Astounding]

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.