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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.