DATA Step, Macro, Functions and more

How do I get result using substring in proc sql?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

How do I get result using substring in proc sql?

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


Accepted Solutions
Solution
‎10-08-2016 07:17 AM
Super User
Posts: 11,343

Re: How do I get result using substring in proc sql?

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


All Replies
Solution
‎10-08-2016 07:17 AM
Super User
Posts: 11,343

Re: How do I get result using substring in proc sql?

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.

Occasional Contributor
Posts: 5

Re: How do I get result using substring in proc sql?

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

 

 

Super User
Posts: 11,343

Re: How do I get result using substring in proc sql?

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'

Occasional Contributor
Posts: 5

Re: How do I get result using substring in proc sql?

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

 

Super User
Posts: 7,811

Re: How do I get result using substring in proc sql?

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 7,811

Re: How do I get result using substring in proc sql?

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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