|
Chad,
You can use the SUBSTR function solution previously presented.
However, I think that you might prefer to employ the SCAN
function. When employed as
ID=SCAN(policy_num, -1);
the SCAN function will return the first delimited word starting
at the end of the variable policy_num. With a negative integer,
the SCAN function will work backward from the end of the string
or variable specified in the first argument when selecting off
delimited words. This would seem to me exactly the problem that
you have specified.
Dale
--- Chad Webb <purplepride@GMAIL.COM> wrote:
> SAS-L,
>
> I found a slight problem yesterday and am soliciting your help with
> it. Take a look at the following code:
>
> proc sql;
> connect to db2(ssid=dbpw);
> create table prod_data_cdw as
> select * from connection to db2
> (select substr(a1.i_cvm_ent,5,10) as I_PLCY_NMBR, a2.c_cvm_ent_val
> as OLD_CVM_PROFIT_BKT,a2.d_true_score as Comp_Date from
> DWPDP002.AGISDB2.dwp_cvm_ent_scores a2 inner join
> DWPDP002.AGISDB2.dwp_cvm_surr_assc a1 on
> a1.c_part_key = a2.c_part_key and
> a1.i_sk_cvm_ent = a2.i_sk_cvm_ent and
> %str(%'&begin_date%') <= a2.d_true_score and
> a2.d_true_score <= %str(%'&end_date%'));
> quit;
>
> My first value I am selecting is a substring of a value that would
> represent someone's policy/id number. I was originally told that
> there
> was a 3 character prefix, followed by a space, then the 10 digit id.
>
> My problem is that what I was told and what it true are two different
> things. A policy can have multiple "alpha-numeric" 3 byte prefix
> separated by a space and then followed by the 10 digit id. Examples:
> 'AAP 0011223344', 'AP1 00A 1234567890'
>
> I first thought to use compress and remove the characters and spaces,
> but I would be wrong because a prefix could have a number.
>
> In Teradata (this code is currently for DB2) there is a function
> called rtrim which takes the x number of bytes starting from the
> right.
>
> Is there something equivalent to rtrim in db2 or SAS that I can use?
> Is there another (maybe better) approach to this?
>
> Any help would be very appreciated.
>
> Thanks,
> Chad
>
=====
---------------------------------------
Dale McLerran
Fred Hutchinson Cancer Research Center
mailto: dmclerra@NO_SPAMfhcrc.org
Ph: (206) 667-2926
Fax: (206) 667-5977
---------------------------------------
__________________________________
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail
| 
