BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Augusto
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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;

View solution in original post

6 REPLIES 6
art297
Opal | Level 21

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
Obsidian | Level 7

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

Augusto
Obsidian | Level 7

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
Opal | Level 21

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
Rhodochrosite | Level 12

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
Opal | Level 21

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 860 views
  • 3 likes
  • 3 in conversation