PROBLEM IN DATA INTEGRATION STUDIO

[Augusto]

Hi dear people,

Could anyone help me with this problem.

I have these values from the PRODUCT_TABLE

COD_PROD

000042568

000003690

0000086M5

0000JI8710

00008971M

000M564M1

I need to create a new variable with these values

COD_PROD_NEW

42568

3690

86M5

JI8710

8971M

M564M1

SO TAKE THE  ZEROS OUT OF THE BEGINNING.

THE PROBLEM IS THAT I'm using D.I (Data Integration).  I AM NEW IN D.I AND I SEEMS THAT I CANNOT USE LOOP AND NOT DATA STEP. ONLY PROC SQL.

[art297 · Accepted solution]

Since you can use proc sql, you could use a regular expression.  E.g.:

data have;

  length zero $10;

  input zero $ name $;

  cards;

00124 ancd

00002154 cdsfs

000245755 aswda

000100245 cdsew

;

proc sql;

  create table want as

    select prxchange("s/^0+//", 1, zero) as zero, name

      from have

  ;

quit;

[Augusto]

Art very very good. You are the best. thanks a lot.

[Augusto]

art i have a question.

Why to use these values "s/^0+//" for prxchange function?

Does this parameter will be always like that?,

I see why use the value for 0 but how about the s/^+//?

I would like to handle with all special characters.  Just using these values is enough?

I'm afraid of missing or cut off other values.

Thanks

[art297]

Augusto,

You can find the documentation at:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a002291852.htm

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a003288497.htm

Looking at the statement, from left to right, indicates the following (although realize that I, too, am just learning these functions, thus my terminology may not be correct):

the "s/whatever/ is indicating

  s/  that the definition of what we want to find follows

   ^    that we are only looking at the start of the string

   0+ for any number of zeros at the start of the string

   /    that the above represents what we are looking for

   //   that we want the part of the string we defined to be replaced with nothing (i.e., removed)

Thus, all it is doing is removing any zeros that may exist at the start of the string.

[Peter_C]

finding regex kind of unfathomable:smileyconfused: I like to see how complex an alternative might be.

The following uses the VERIFY() function to locate the first character that is not a zero and uses that position for substringing. Should there be only zeros the function returns 0 as the position, which is a problem for substr(). I assumed the value should become blank when entirely filled with zeros, but if another value is more appropriate then the change to the code should be straightforward.

Using Art's test data, extended for some special cases (alphabeticals, no zeros and only zeros)

data have;

   length zero $10;

   input zero $ name $;

cards;

00124 ancd

00002154 cdsfs

000245755 aswda

000100245 cdsew

00a00 678

ASDFG FGHJKL

0000000000 ertyui

;

proc sql;

  create table want as

  select case when compress( zero,'0' ) EQ ' ' then ' '

             else substr( zero, verify( zero, '0') )

          end length= 10 as notZ

        , zero

        , name

      from have

  ;

quit;

[art297]

Peter,

Actually, in this case, using verify actually produces code that runs faster.  However, if it were a more complex pattern than just "beginning with zeros", I think that the potential functionality of regex might be difficult to duplicate.

Regardless, IMHO, both are useful tools and worth learning.