topic Re: How do I isolate select digits from a numeric key based on varying conditions? in SAS Programming

How do I isolate select digits from a numeric key based on varying conditions?

Gaurangl — Tue, 16 Apr 2019 14:21:37 GMT

I am trying to extract a Country ID from a numeric product key which has 2 types:
1. A 7-8 digit key with first 2-3 digits signifying country ID.
i.e. For US with Country code 36, product key= 36XXXXX

For INDIA with Country code 121, product key= 121XXXXX

2. A 13 digit key with 2nd, 3rd , 4th digit signifying country ID
i.e. For US , Product key=X036XXXXXXXXX

For IN, Product Key= X121XXXXXXXXX

Using the following code to get this:

data want;
set have;
if length(key)>8 THEN COUNTRY_ID=substr(key), 2,3);
else COUNTRY_ID=substr(key,1,length(key)-5);
run;

It works for the 1st type with 7-8 digit product key but not for the longer 13 digit keys. It shows a decimal in the country id
i.e. For US, X036XXXX whereas it should be just 36.

Do I need to change data type??

Using SAS EG 7.1

Re: How do I isolate select digits from a numeric key based on varying conditions?

ballardw — Tue, 16 Apr 2019 14:33:31 GMT

First thing you need to pay attention when your LOG shows lines like:

NOTE: Numeric values have been converted to character values at the places given by:
      (Line):(Column).
      14:13

Since you did not specify a format to convert a numeric to character when the Length function is called for the numeric value then SAS does a type conversion and defaults to using a BEST12. format. So the length returned to begin with is not what think was returned. Please see this example:

data example;
   input x;
   l=length(x);
datalines;
1
12
123
1234
12345
123456
1234567
12345678
123456789
1234567890
12345678901
123456789012
1234567890123
;
run;

The "Length" for all of those values is 12 because of the way the automatic conversion is done.

Better would have been to insure the values were read that they were read as character values if you are not going to do arithmetic with them.

See this for a way to get the proper Length comparison:

data example;
   input x;
   l=length(put(x,15. -L));
datalines;
1
12
123
1234
12345
123456
1234567
12345678
123456789
1234567890
12345678901
123456789012
1234567890123
;
run;

The -L says to left justify the result of PUT.

You also need to use the same sort of PUT (Key,15. -L) in the SUBSTR function argument so you aren't getting leading spaces and such.

Re: How do I isolate select digits from a numeric key based on varying conditions?

Shmuel — Tue, 16 Apr 2019 14:40:32 GMT

Is COUNTRY_ID defined as numeric type or as char type?

If it is numeric define format as 3. to avoid leading zero.

If it is char type then define:

if length(key)>8 THEN COUNTRY_ID=put(input(substr(key, 2,3),3.),3.);