topic Creating multiple numeric columns from multiple character columns in SAS Programming

Creating multiple numeric columns from multiple character columns

sahoositaram555 — Thu, 17 Aug 2023 19:43:01 GMT

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.

Re: Creating multiple numeric columns from multiple character columns

PaigeMiller — Thu, 17 Aug 2023 19:47:27 GMT

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.

Re: Creating multiple numeric columns from multiple character columns

ballardw — Thu, 17 Aug 2023 22:57:43 GMT

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.

Re: Creating multiple numeric columns from multiple character columns

Tom — Thu, 17 Aug 2023 23:07:43 GMT

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