Substring a numeric in where clause

Reply
Contributor
Posts: 74

Substring a numeric in where clause

I need to extract the first 6 digits of an id number in a z16 format.  I need to remove the leading zeros so any id number that is not a full 16 I will still get the first 6 digits.

And if possible I need this in a where statement in my data step so that I only pull records with the first 6 digits of the id that = 598758

It needs to work in a sas mainframe program.


Can that be done?


Thanks,

Valued Guide
Posts: 858

Re: Substring a numeric in where clause

I think this is what you are looking for.  Let me know if this helps:

data have;

input id;

cards;

1

5

1111222233334444

2222333344445555

321654

987654

;

run;

data prep;

set have;

id2 = put(id,z16.);

run;

data want;

set prep;

new_var=substr(scan(id2,1,'0'),1,6);

run;

Valued Guide
Posts: 858

Re: Substring a numeric in where clause

I missed the where part:

data want;

set prep;

where substr(scan(id2,1,'0'),1,6) = '598758';

run;

Super User
Super User
Posts: 7,720

Re: Substring a numeric in where clause

Hi,

data have; 

  format a z16.;

  a=12345678; output;

  a=5654; output;

run;

data want;

  set have;

  b=substr(left(put(a,8.)),1,6);

run;

Super User
Posts: 9,867

Re: Substring a numeric in where clause

Code: Program

data have;
input id;
want=int(id/(10**(int(log10(id))-5)));
cards;
1111222233334444
2222333344445555
321654
987654
;
run;

Xia Keshan

Valued Guide
Posts: 858

Re: Substring a numeric in where clause

Elliot,

If your variable is numeric your where clause only asks for 598758, the leading zeros do not change the number. 

Contributor
Posts: 74

Re: Substring a numeric in where clause

this is an example of my data step:

data want;

SET data1;                                                      

  (KEEP= ID CLIENT REGION CATAGORY DAYS    

        WHERE=(DAYS >= 120                                          

           and substr(scan(ID,1,'0'),1,6'598758'                                       

          )

   )                                                           

;

run;


I only want to pull in the id's that start with those 6 digits, if the id has less than 16 digits the id is populated with leading zeros.  In my testing if the id has the leading zeros then when I extract out the first 6 the leading zeros are included.  I need to remove the leading zeros if there are any and just pick up the actual id.  I was hoping to do this within the where so that I did not need to pull in a large population then do a bunch of conversion just to then remove because they did not begin with 598758.


Hope that makes sense.


I get an error on the scan above says function scan requires a character expression as argument 1.


Thanks,

Super User
Super User
Posts: 7,720

Re: Substring a numeric in where clause

Where =(days >=120 and substr(left(put(a,8.)),1,6)="598758").

However that said, if that part of the id is important, say it is the siteid part of a longer id, consider making it a separate variable, or use a character id with a delimiter.  Then its available for all programs and makes it easier to find.

Valued Guide
Posts: 858

Re: Substring a numeric in where clause

Then your variable is a number, not a character and you don't need the substr or scan.  0000598758 is the same as 598758, if you are looking for a number just use 'where id = 589758'.

You can use substrn to get a substring out of a numeric variable but I don't think that would be necessary in this case.

Super User
Super User
Posts: 7,720

Re: Substring a numeric in where clause

I was assuming the id is actually, longer than the part given, i.e. 000059875812345.  Maybe I misunderstood.

Contributor
Posts: 74

Re: Substring a numeric in where clause

the majority of the id's are a full 16 digits.  There are occasional id's that have fewer than 16, therefore have leading zeros.  I can get what I want by doing some conversion and removing after that but I was trying use the where statement so that I did not pull in thousands of records I do not need because they don't start with 598758. 

Thanks,

Super User
Super User
Posts: 7,720

Re: Substring a numeric in where clause

So this should work then:

Where =(days >=120 and substr(left(put(a,8.)),1,6)="598758");


Super User
Posts: 5,365

Re: Substring a numeric in where clause

It's still not clear whether ID is numeric or character.  On a mainframe, there should be room to store 16 significant digits as numeric.

If ID is numeric, you could use:

where (left(put(id,16.)) =: '598758')

If it is character, you could add a conversion step:

where (left(put(input(id,16.),16.))) =: '598758')

And this would not necessarily still work if you switched off of a mainframe.  As before, don't forget the colon.

Good luck.

Good luck.

Super User
Posts: 5,365

Re: Substring a numeric in where clause

This is clumsy, but it will do until a better solution comes along:

where=(days >= 120 and id in: ('598758', '0598758', 00598758', '000598758', '0000598758', '00000598758',

'000000598758', '0000000598758', '00000000598758', '000000000598758', '0000000000598758')

Be sure to include the colon following the IN operator.

Super User
Super User
Posts: 6,845

Re: Substring a numeric in where clause

If the variable is numeric.  And you want to find values where the first 6 digits (ignoring leading zeros that would appear if formatted with Z16. format) match some pattern then you can just use PUT() function and colon modifier on the equality test.

where left(put(id,16.)) =: '598758' ;

That would match values like:

598758

5987586

59875864

.....

Ask a Question
Discussion stats
  • 15 replies
  • 1027 views
  • 1 like
  • 7 in conversation