06-04-2015 08:09 AM
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?
06-04-2015 08:27 AM
I think this is what you are looking for. Let me know if this helps:
id2 = put(id,z16.);
06-04-2015 09:36 AM
this is an example of my data step:
(KEEP= ID CLIENT REGION CATAGORY DAYS
WHERE=(DAYS >= 120
and substr(scan(ID,1,'0'),1,6) '598758'
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.
06-04-2015 09:42 AM
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.
06-04-2015 09:43 AM
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.
06-04-2015 05:15 PM
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.
06-05-2015 09:31 AM
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.
06-04-2015 09:44 AM
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.
06-05-2015 10:49 AM
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: