topic Re: How can I code for a uniform string length where there is variation in data entry in SAS Enterprise Guide

How can I code for a uniform string length where there is variation in data entry

gregor1 — Wed, 11 Oct 2017 21:29:37 GMT

While using EGP 6.1, I am trying to get a common look for each row of a specific column where the data varies in length. The full ID Number has a prefix of "00" then is followed by 9 other digits. However, some people enter the full ID Number while others only enter the last 9 digits. I am trying to just return the last 9 digits but am having trouble finding what function to use. I have been trying to use the SCAN, the SUBPAD, the combination of SUBPAD with REVERSE functions, etc. and am getting nowhere fast. To make matters more difficult, some of the last 9 digits do begin with "00" which I want to include.

Here is an example of data and what I am getting back (the first column is the ID Number as people have entered (I tried to reverse the order and then do a SUBSTRN with a limit of 9. As you can see, the good data that started with just 9 digits, now only displays 7 digits but shows a length of 9. On the other hand, the numbers too long numbers that showed 11, are now correctly showing just 9 digits.) Any thoughts on the best practice to limit the number of digits to the last 9? Ideally I would like to start from the end of the number and move to the left 9 digits and return only those 9 digits.

ID Number	Reversed ID Number	SUBSTRN ID Number	LNGTH of SUBSTRN
000359260	062953000	0629530	9
001094641	146490100	1464901	9
00161892070	07029816100	070298161	9
001750341	143057100	1430571	9
001750357	753057100	7530571	9
01000009831	13890000010	138900000	9
01000047161	16174000010	161740000	9
01000053759	95735000010	957350000	9
016506959	959605610	9596056	9

Re: How can I code for a uniform string length where there is variation in data entry

Tom — Wed, 11 Oct 2017 22:01:50 GMT

Since you only have 9 digits you could just convert the string to a number and then back to a string using the Z format to make sure it prefixes zeros for the small numbers.

want = put(input(id_number,32.),Z9.);

Re: How can I code for a uniform string length where there is variation in data entry

Kurt_Bremser — Thu, 12 Oct 2017 06:48:09 GMT

How about this:

data have;
input idnum :$11.;
cards;
000359260
001094641
00161892070
001750341
001750357
01000009831
01000047161
01000053759
016506959
;
run;

data want;
set have;
length new_idnum $9;
new_idnum = substr(put(input(idnum,11.),z11.),3);
run;

Note how I presented example data in a data step, so that others (and you!) only need to copy/paste and submit the code to recreate the dataset.

Re: How can I code for a uniform string length where there is variation in data entry

gregor1 — Thu, 12 Oct 2017 12:49:59 GMT

Hi Kurt,

That makes sense, except in the full dataset, I have thousands of rows with separate ID Numbers.

Re: How can I code for a uniform string length where there is variation in data entry

Kurt_Bremser — Thu, 12 Oct 2017 12:53:25 GMT

@gregor1 wrote:

Hi Kurt,

That makes sense, except in the full dataset, I have thousands of rows with separate ID Numbers.

And? What works on 10 rows also works on 10 billion rows.

Re: How can I code for a uniform string length where there is variation in data entry

gregor1 — Thu, 12 Oct 2017 13:08:20 GMT

I get it, so I don't need to enter the individual ID Numbers in the code?

Re: How can I code for a uniform string length where there is variation in data entry

Kurt_Bremser — Thu, 12 Oct 2017 13:17:46 GMT

@gregor1 wrote:

I get it, so I don't need to enter the individual ID Numbers in the code?

No, not at all. That first data step is just creating some example data on the fly from the examples you gave in your initial post. Instead of the cards section, one could also use an infile statement to read from an external file.

Using a datastep with cards or datalines is the preferred way to present example data here, as it allows easy (and exact) recreation of data by everyone reading the posts.

Just apply the second data step to your dataset, and adapt the variable names to your needs.

Re: How can I code for a uniform string length where there is variation in data entry

gregor1 — Thu, 12 Oct 2017 13:20:59 GMT

Great...I will give it a try. Thanks Kurt!

Re: How can I code for a uniform string length where there is variation in data entry

gregor1 — Thu, 12 Oct 2017 21:26:32 GMT

Thanks Kurt...this worked great!