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
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.
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.);
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.