I have this below dataset
data have;
input col1 $1-8 col2 $9-16 col3 $17-25 col4 $26-34 col5 $35-41;
datalines;
5(25.8) 2(6.25) 8(25.0) 10(14.4) 9(25.8)
56(12.3) 1(3.13) 19(14.4) 56(12.3) 0
87(6.0) 1(3.13) 11(34.4) 1(3.13) 0
90(87.0) 2(6.25) 1(3.13) 1(3.13) 0
;
run;
I would like to take the nuemric part from each of these columns exist, and create nuemric columns for each that contains only the value before bracket starts "(".
eg: for the 1st row there will be 5 numeric columns with value in first column will be 5 and in the last column the value will be 9.
I have written a code to solve this,
data want;
set have;
array char(*) COL1 COL2 COL3 COL4 COL5; /*columns I have */
array new(*) COL1N COL2N COL3N COL4N COL5N;/*numeric columns to be produced*/
do i=1 to dim(char);
if char[i] ~="" then new[i]=scan(char[i],1,'(');
end;
run;
but I'm getting ERROR: Array subscript out of range at line 35 column 22.
and for the last column since I have '0', need to add a consition to handle the same.
When you get an error in the log, SHOW US the log. We need to see the entire log for this data step. Please copy the log as text and paste it into the window that appears when you click on the </> icon.
Suggestion: Do not use those Col1N Col2N names. It makes it hard to use several of the tools that SAS provides to treat variables as lists. If you are using N to indicate a numeric variable it may be easier on you in the long run to use NCol1.
For one thing you can define the list of variable as
Array new(*) Ncol1-Ncol5 ;
You may also want to try copying your pasted code for the first data step back into your SAS session and running it.
The values I get include spaces in the middle of several variables because the column counts are not correct for the code as shown. For example the 10 that should start variable Col4 on the first row has the 1 in column 25. Which means it is read into Col3 with a space preceding. With the first digit read into the end of the previous variable then some of your values in Col4 (or Col5) in the example have nothing before the (. So you get missing values in the numeric because you converted a zero length string with no characters into a "number".
It also quite often happens that you will have issues with the automatic conversion of character to numeric.
You always want to check the result of any such conversion if you see this in your log:
NOTE: Character values have been converted to numeric values at the places given by: (Line):(Colum
You do not need any "condition" to read the 0 values without the (. Example (note use of INPUT to do an explicit character to numeric which expects at most 2 digits to use for numeric value).
data example; x = '0'; y = input(scan(x,1,'('),2.); run;
If the end of the string is encountered before any specified delimiter then the whole string is returned as the first SCAN function result.
Seems trivial to me.
data have;
length col1-col5 $20;
input col1-col5;
datalines;
5(25.8) 2(6.25) 8(25.0) 10(14.4) 9(25.8)
56(12.3) 1(3.13) 19(14.4) 56(12.3) 0
87(6.0) 1(3.13) 11(34.4) 1(3.13) 0
90(87.0) 2(6.25) 1(3.13) 1(3.13) 0
;
data want;
set have;
array col[5];
array ncol[5];
do index=1 to dim(col);
ncol[index]=input(scan(col[index],1,'('),32.);
end;
drop index;
run;
Result
Obs col1 col2 col3 col4 col5 ncol1 ncol2 ncol3 ncol4 ncol5 1 5(25.8) 2(6.25) 8(25.0) 10(14.4) 9(25.8) 5 2 8 10 9 2 56(12.3) 1(3.13) 19(14.4) 56(12.3) 0 56 1 19 56 0 3 87(6.0) 1(3.13) 11(34.4) 1(3.13) 0 87 1 11 1 0 4 90(87.0) 2(6.25) 1(3.13) 1(3.13) 0 90 2 1 1 0
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.