topic Re: Help converting data using translate in SAS Programming

Help converting data using translate

Mofro13 — Mon, 10 Aug 2020 19:23:49 GMT

Hello

My first post. I am using SAS 9.4 in Windows. I am importing data from an Excel file. The data is alpha-numeric. The variables range in length from 2 to 7 characters. For example:

151

534

345697

1A875

The variables need to be 7 digits with leading zeroes. Assuming the variable I am importing is names 'variable1', the code i am using is:

proc import datafile="xxxxxxx.xlsx"
out=dataset1
dbms=xlsx ;
run;

data dataset2;
set dataset1;
format variable2 $7.0 ;
variable2 = translate(right(variable1),"0"," ");

run;

The variables in the output set are 7 digits but it leaves off the last character. Using the above data set, the output is:

0000015

0000053

0034569

0001A87

I just can't get the right output and I know it worked last month!! Aiiiiii

Any advice?

thanks

Re: Help converting data using translate

s_lassen — Mon, 10 Aug 2020 19:39:37 GMT

The problem is probably that the VARIABLE1 variable has a length of 8. So when you right-align it, you get one more leading zeros than you want, and the last character is cut off when assigning the value to VARIABLE2 which has a length of 7.

The easiest way out is probably just to drop the first leading zero:

variable2 = substr(translate(right(variable1),"0"," "),2);

Re: Help converting data using translate

Mofro13 — Mon, 10 Aug 2020 19:55:07 GMT

That did it. Thanks for the help!!