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