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

How do I get result using substring in proc sql?

SAS_UA — Fri, 07 Oct 2016 19:40:11 GMT

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

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

ballardw — Fri, 07 Oct 2016 19:51:44 GMT

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.

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

SAS_UA — Fri, 07 Oct 2016 22:14:19 GMT

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

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

ballardw — Fri, 07 Oct 2016 22:33:12 GMT

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'

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

Kurt_Bremser — Sat, 08 Oct 2016 09:02:01 GMT

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

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

SAS_UA — Sat, 08 Oct 2016 11:26:34 GMT

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

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

Kurt_Bremser — Sat, 08 Oct 2016 17:42:06 GMT

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.