Hi,
The Orders table in library sales has a variable CustomerId char(3).
On running below both the SQL, creates table with no result in variable NewID.
Environment: SAS base 9.2
Log shows note as : NOTE: Invalid argument 2 to function SUBSTR. Missing values may be generated
How to get result in the variable NewId as 00123 from 123.
Option 1 proc sql; create table NewList as select unique substr(cat('00000',CustomerId),-5,5) as NewID, customerid from Sales.orders; quit; Option 2 proc sql; create table NewList as select unique input(substr(cat('00000',CustomerId),-5,5),$char5.) as NewID, customerid from Sales.orders; quit;
Thanks,
UA
You cannot start 5 characters before the start of the string. 1 is the lowest value allowed to start from.
You would likely get better results with: put(customerid,z5.) as newid
if your customerid is indeed numeric.
I would wager that somewhere in the begining that a data set was created using Proc Import that treated your customerid as numeric. Or possible Excel was involved and did the same thing, stripping leading zeroes.
You cannot start 5 characters before the start of the string. 1 is the lowest value allowed to start from.
You would likely get better results with: put(customerid,z5.) as newid
if your customerid is indeed numeric.
I would wager that somewhere in the begining that a data set was created using Proc Import that treated your customerid as numeric. Or possible Excel was involved and did the same thing, stripping leading zeroes.
Hi Ballardw,
Need the numbers from right side with left side zero padding, so that can use the table to connect with other data tables via proc sql.
1) customer id is character.
2) substr gives error on using minus sign for extraction e.g. substr(cat('00000',customerid),-1,6) does not work.
Is there a way to extract numbers from right, after concatenating ('00000',Customerid) through SAS and
store value in the table as 000123 or 000012 based on customerid value ?
Thx
UA
Did you attempt the code line I provided? PUTting data with Z format pads the displayed value with zeroes on the left.
PUT(123, z5.) as NewId will yield a character variable of '00123'
Hi Ballardw,
Thanks, thought z is only used for numeric. tested it worked.
KurtBremser:
Thanks to you also, for the the datastep and clarifying minus sign only works with scan().
UA
You can use a numeric format on a character variable, but that forces SAS to make an automatic conversion.
I'd rather do the conversion explicitly:
x = put(input(x,best.),z5.);
This prevents the NOTE about the conversion in the log and shows the next one who has to maintain the code that you knew what you were doing.
Hint: the next one might be you.
- use the z. format, as @ballardw already recommended.
- to extract the last x characters from a string, use the length() function to find the end:
data _null_;
x1 = '00000123';
x = 5;
x2 = substr(x1,length(x1)-x+1);
put x2=;
run;
Negative indexes (to start from the end) only work in functions like scan(), but not in substr().
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.