BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SAS_UA
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

6 REPLIES 6
ballardw
Super User

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.

SAS_UA
Fluorite | Level 6

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

 

 

ballardw
Super User

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'

SAS_UA
Fluorite | Level 6

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

 

Kurt_Bremser
Super User

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.

Kurt_Bremser
Super User

- 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().

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 47783 views
  • 0 likes
  • 3 in conversation