Extract Number From String and Convert to Number

[Jeff_DOC]

Hello.

I have a problem I'm hoping someone can help me with. I have a large list of the type at the bottom. I need to parse out the numbers from the string in var2 and then compare the result to a range. For instance, the range may be from 13 to 35 and I need to find which desks fit into that range. So if I can parse out the numbers and they determine if they are within a particular range that'd be perfect. I've attempted to use:

data test_1;

set have;

     if compress(var2,'', 'A') >= &range_1. and compress(var2,'', 'A') <= &range_2.  then test = 'YEAH';

run;

 to parse out the numbers and they come out alpha. I've also tried:

data test_1;

  set have;

    if input(compress(var2,'', 'A'), 8.) >= &range_1. and input(compress(var2,'', 'A'), 8.) <= &range_2. then test = 'YEAH';

run;

When I use either method above in order to convert the result to a number so I can use GE and LE to compare to the criteria range I get an error: 

     ERROR 388-185: Expecting an arithmetic operator.

My macro variables are defined as:

     %let range_1 = 13;
     %let range_2 = 24;

so they appear to be numeric so I'm not sure where my error might be?

Thanks to anyone who can take the time to help me out.

var1	var2	WANT
ROOMA	DESK01A	1
ROOMA	DESK01B	1
ROOMA	DESK05B	5
ROOMA	DESK06A	6
ROOMB	DESK09A	9
ROOMB	DESK09B	9
ROOMC	DESK01B	1
ROOMC	DESK05A	5
ROOMD	DESK10B	10
ROOME	DESK15A	15
ROOME	DESK15B	15
ROOME	DESK16	16

[sbxkoenk]

data _NULL_;
 a='DESK05B';
 /* Compressing Space Characters by Using a Modifier   */
 b=compress(a, '', 's');
 /* Keeping Characters in the List by Using a Modifier */
 c=compress(b, '0123456789', 'k');
 put c=;
 d=input(c,2.);
 put d=;
 if 3 < d < 7 then test = 'YEAH';
 put test=;
run;

Ciao,

Koen

[PaigeMiller]

compress(var2,'', 'A')

The COMPRESS function produces a character string, in this case a character string of the letters in VAR2. For the first row of your data set you get the character string '01'. It is not a number, even if it looks like a number to you, so SAS does not know how to evaluate if the character string '01' is greater than &range_1, which is a number. SAS cannot compare characters strings to numbers. So to fix this, you want to convert the character string from var2 into an actual number. You do this by

input(compress(var2,'', 'A'),3.)

which turns the character string into the actual number 1. Then it can be compared to the number in &range_1. This will handle numbers up to 3 digits wide (i.e <= 999).

My macro variables are defined as:

%let range_1 = 13;
%let range_2 = 24;

so they appear to be numeric so I'm not sure where my error might be?

The values of macro variables are always text, no matter that they look like numbers. The only way they become numbers is if you use them in a way that SAS recognizes them as numbers. In this case, when SAS evaluates the code, it replaces &range_1 with 13, and so then the code actually becomes 

compress(var2,'', 'A') > 13

and then SAS recognizes 13 as a number from the context. Do you know why in the above line that 13 is treated as a number and not a character string?

@Jeff_DOC you're not new here. Please be a good citizen of the community and help us just as we are trying to help you. When you get errors in the log, then SHOW US THE LOG. We need to see the entire log for the DATA step (or PROC) that has the error from now on, without having ask.

[ballardw]

It is a good idea to paste your code by copy from your editor and paste into a text box opened on the forum using the </> icon above the message window. The forum software will reformat text when pasted into the main message window and may hide the actual problem because of the conversion. 

From ancient behaviors with some keyboards I don't trust >= and =< and use the SAS GE and LE comparisons in code. Sometimes a >= or <= may just happen to be a single character in an extended character set that SAS won't use for comparisons.

It is also a good idea to provide data in the form of a working data step as when I create data set such as with this data step:

data have;
  input var1 $ var2 $;
datalines;
ROOMA 	DESK01A 	
ROOMA 	DESK01B 	
ROOMA 	DESK05B 	
ROOMA 	DESK06A 	
ROOMB 	DESK09A 	
ROOMB 	DESK09B 	
ROOMC 	DESK01B 	
ROOMC 	DESK05A 	
ROOMD 	DESK10B 	
ROOME 	DESK15A 	
ROOME 	DESK15B 	
ROOME 	DESK16 	
;

And get this log for YOUR code:

101    %let range_1 = 13;
102    %let range_2 = 24;
103  data test;
104     set have;
105     if input(compress(var2,'', 'A'), 8.) >= &range_1. and
105! input(compress(var2,'', 'A'), 8.) <= &range_2. then test = 'YEAH';
106  run;

NOTE: There were 12 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.TEST has 12 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

which has no error. So one suspects something in your data may be involved.

Resulting data set:

var1	var2	test
ROOMA	DESK01A
ROOMA	DESK01B
ROOMA	DESK05B
ROOMA	DESK06A
ROOMB	DESK09A
ROOMB	DESK09B
ROOMC	DESK01B
ROOMC	DESK05A
ROOMD	DESK10B
ROOME	DESK15A	YEAH
ROOME	DESK15B	YEAH
ROOME	DESK16	YEAH

The variables Var1 and Var2 are length 8. If your variables  white space values like leading spaces, tabs or NULL characters (ASCII 255) then we can't see those and may be reformatted by the forum software to blanks.  

I would try using :   compress(var2,'', 'dk') to keep only digits. This would protect against non-displayed characters like Null.

With those thoughts in mind, when I create a data set with this code:

data have;
  input var1 $ var2 $;
datalines;
ROOMA 	DESK01A 	
ROOMA 	DESK01B 	
ROOMA 	DESK05B 	
ROOMA 	DESK06A 	
ROOMB 	DESK09A 	
ROOMB 	DESK09B 	
ROOMC 	DESK01B 	
ROOMC 	DESK05A 	
ROOMD 	DESK10B 	
ROOME 	DESK15A 	
ROOME 	DESK15B 	
ROOME 	DESK16 	
;

Also any error message should be copied from the log with the code of the procedure or data step that generates the error along with the error and all other notes or warnings for that step. Copy the text from the log, open a text box using the </> and paste. Many of those errors will have diagnostics that indicate where the error is found.

As an aside, I strongly recommend using numeric results in stead of 'Yes' or similar for comparisons especially if the result is binary Yes/No, True/False, Present/Not present or similar.

You could use

Test = value1 LE TestValue LE value; 

Test would have value 1 when true and 0 when false.  It is often much easier to get reportable values from the numeric values a Sum of Test would have the count of true values, mean would be the percentage true values as as decimal. Questions like all true, all false, more true than false are also often easier to determine using sum of the variable compared to count of values, N statistic, in proc summary, means, report or tabulate than dealing with character values.

[Patrick]

Something like below should do.

data have;
  input var1 $ var2 $;
datalines;
ROOMA 	DESK01A 	
ROOMA 	DESK01B 	
ROOMA 	DESK05B 	
ROOMA 	DESK06A 	
ROOMB 	DESK09A 	
ROOMB 	DESK09B 	
ROOMC 	DESK01B 	
ROOMC 	DESK05A 	
ROOMD 	DESK10B 	
ROOME 	DESK15A 	
ROOME 	DESK15B 	
ROOME 	DESK16 	
ROOME 	DESK 	
;

proc format;
	value range
	.   			= 'missing'
	low - 5 	= 'Range 1'
	5 -  10 	= 'Range 2'
	10 - high	= 'Range 3'
	;
run;

data want;
	set have;
	nums=input(scan(var2,1,,'kd'),best32.);
run;

proc sql;
	select var1, var2, nums as nums1 format=best32., nums as nums2 format=range.
	from want
	;
quit;

[Tom]

Your first program does what it asked it to do.

data have;
  input var1 $ var2 $ WANT ;
cards;
ROOMA  DESK01A 1
ROOMA  DESK01B 1
ROOMA  DESK05B 5
ROOMA  DESK06A 6
ROOMB  DESK09A 9
ROOMB  DESK09B 9
ROOMC  DESK01B 1
ROOMC  DESK05A 5
ROOMD  DESK10B 10
ROOME  DESK15A 15
ROOME  DESK15B 15
ROOME  DESK16  16
;

%let range_1 = 13;
%let range_2 = 24;
data test_1;
  set have;
  if compress(var2,'', 'A') >= &range_1. 
  and compress(var2,'', 'A') <= &range_2.  
  then test = 'YEAH';
run;
proc print;
run;

 Results

Obs	var1	var2	WANT	test
1	ROOMA	DESK01A	1
2	ROOMA	DESK01B	1
3	ROOMA	DESK05B	5
4	ROOMA	DESK06A	6
5	ROOMB	DESK09A	9
6	ROOMB	DESK09B	9
7	ROOMC	DESK01B	1
8	ROOMC	DESK05A	5
9	ROOMD	DESK10B	10
10	ROOME	DESK15A	15	YEAH
11	ROOME	DESK15B	15	YEAH
12	ROOME	DESK16	16	YEAH

But why does it work?  Because SAS helpfully automatically converted the strings like '1' and '15' that your COMPRESS() function call generated into numbers:

 90         data test_1;
 91           set have;
 92           if compress(var2,'', 'A') >= &range_1.
 93           and compress(var2,'', 'A') <= &range_2.
 94           then test = 'YEAH';
 95         run;
 
 NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
       92:6   93:7   

If you want to convert the value to numbers yourself then use the INPUT() function.

data test_2;
  set have;
  test2 = input(compress(var2,' ','A'),32.);
  check= (test2 = want);
run;

Result

Obs	var1	var2	WANT	test2	check
1	ROOMA	DESK01A	1	1	1
2	ROOMA	DESK01B	1	1	1
3	ROOMA	DESK05B	5	5	1
4	ROOMA	DESK06A	6	6	1
5	ROOMB	DESK09A	9	9	1
6	ROOMB	DESK09B	9	9	1
7	ROOMC	DESK01B	1	1	1
8	ROOMC	DESK05A	5	5	1
9	ROOMD	DESK10B	10	10	1
10	ROOME	DESK15A	15	15	1
11	ROOME	DESK15B	15	15	1
12	ROOME	DESK16	16	16	1

Two things to note.

1) I explicitly asked COMPRESS() to remove spaces as well has letters.  You might also instead try tell it to only keep digits.

2) I used the 32. informat to read the generated string.  The INPUT() function does not care if the width of the informat being used is larger than the length of the string being read.  And since 32 is the maximum width that the numeric informat supports it is best to use that when you don't know how long the string being read might be.