topic Re: Extracting numbers from a mixed variable in SAS Programming

Extracting numbers from a mixed variable

sms1891 — Mon, 09 Apr 2018 21:02:58 GMT

Hi all,

I have a variable (Size_Cyst) recorded with height, length and width dimensions in a single variable. I want to extract them as 3 separate variables (height, length and width). The problem with the original variable is it is recorded as 261x 143 (x245) to give you all an example. 261 corresponds to height, 143 for length and 245 for width.

All I want to do is to create 3 separate variables. The numbers before first x as height, numbers after first x as length, i.e the middle set of numbers. And finally the numbers after second x as width, i.e in the parenthesis without x. Each of these numbers are not fixed, in the sense they may be 3 digit or 2 digit. It varies from ID to ID. However they range only from 2 digit to 3 digit ONLY. And also there are spacing issues between the numbers and X's, it is not captured consistently (e.g: 261x 143 (x245) 198 x 97 (x 160)

If I use compress I can get the numbers but I may lose the actual size. For 261x 143 (x245) i want it to be 261, 143 and 245 but not 26 1143 and 143...

Any help with the SAS code for this extraction would be greatly appreciated.

Thank you in advance!

Here is the example of my data set.

ID Size_Cyst

1 261x 143 (x245)

2 23x 14 (x27)

3 23 x 14 (x 27)

4 243 x 14 (x234)

5 33 x 233 (x 67)

6 89x45x (x148)

Re: Extracting numbers from a mixed variable

Reeza — Mon, 09 Apr 2018 21:06:00 GMT

Use SCAN with the X delimiter since that seems consistent.

Use COMPRESS to keep only digits

Use INPUT to convert to a numeric variable.

The last record seems to have an extra X, which may be problematic, but if you scan in a loop that should work still.

@sms1891 wrote:

Hi all,

I have a variable (Size_Cyst) recorded with height, length and width dimensions in a single variable. I want to extract them as 3 separate variables (height, length and width). The problem with the original variable is it is recorded as 261x 143 (x245) to give you all an example. 261 corresponds to height, 143 for length and 245 for width.

All I want to do is to create 3 separate variables. The numbers before first x as height, numbers after first x as length, i.e the middle set of numbers. And finally the numbers after second x as width, i.e in the parenthesis without x. Each of these numbers are not fixed, in the sense they may be 3 digit or 2 digit. It varies from ID to ID. However they range only from 2 digit to 3 digit ONLY. And also there are spacing issues between the numbers and X's, it is not captured consistently (e.g: 261x 143 (x245) 198 x 97 (x 160)

If I use compress I can get the numbers but I may lose the actual size. For 261x 143 (x245) i want it to be 261, 143 and 245 but not 26 1143 and 143...

Any help with the SAS code for this extraction would be greatly appreciated.

Thank you in advance!

Here is the example of my data set.

ID     Size_Cyst

1      261x 143 (x245)

2      23x 14 (x27)

3      23 x 14 (x 27)

4      243 x 14 (x234)

5      33 x 233 (x 67)

6      89x45x (x148)

Re: Extracting numbers from a mixed variable

sms1891 — Mon, 09 Apr 2018 21:08:29 GMT

Sorry for the typo...last record does not have the extra X. It should be 89x45 (x148)

Re: Extracting numbers from a mixed variable

Astounding — Mon, 09 Apr 2018 21:12:00 GMT

As character variables:

height = scan(size_cyst, 1, 'Xx( )');

length = scan(size_cyst, 2, 'Xx( )');

width = scan(size_cyst, 3, 'Xx( )');

To get these variables as numeric takes a little more work. For example:

height = input(scan(size_cyst, 1, 'Xx( )'), 3.);

Also notice that the space in the third parameter of SCAN is required.

Re: Extracting numbers from a mixed variable

novinosrin — Mon, 09 Apr 2018 21:30:25 GMT

data have;
input ID     Size_Cyst & $20.;
datalines;
1      261x 143 (x245)
2      23x 14 (x27)
3      23 x 14 (x 27)
4      243 x 14 (x234)
5      33 x 233 (x 67)
6      89x45x (x148)
;

data want;
set have;
t=compress(translate(Size_Cyst,' ','x'),' ','kd');
height=scan(t,1);
length=scan(t,2);
width=scan(t,3);
run;

You don;t need input to convert to numeric as sas will do auto conversion when you compute arithmetic

Re: Extracting numbers from a mixed variable

Ksharp — Tue, 10 Apr 2018 13:30:29 GMT

data have;
input ID     Size_Cyst & $20.;
datalines;
1      261x 143 (x245)
2      23x 14 (x27)
3      23 x 14 (x 27)
4      243 x 14 (x234)
5      33 x 233 (x 67)
6      89x45x (x148)
;

data want;
set have;
do i=1 to countw( Size_Cyst,,'kd');
  value=scan( Size_Cyst,i,,'kd'); output;
end;
drop i;
run;