topic Re: Numeric value in a string in SAS Procedures

Numeric value in a string

nickb — Wed, 31 Oct 2012 18:33:54 GMT

I'm working on parsing a text file and I need to find a way to look at the last value in a string, and if it is numeric. The file is large but the data of interest will have the same position.

below is an example of the data. When I copied and pasted it the alignment got out of whack the goal is to look at the last value and check to see if it is numeric. I don't want the record that has _________ in the line. I will use this check in my IF statement.

Data example:

C) D.C.CIRCUITS

EER-126 D.C. CIRCUITS.............. 98/WI A 2.67

N) FUNDAMENTAL MECHANICAL SYSTEMS

EGR-100 FUNDMNTL MECHNCL SKILLS______________________ 1 course needed

C) ROBOTICS IN CIM SYSTEMS

EGR-128 ROBOTICS IN CIM SYSTEMS.... 99/SP A 2

C) COMPUTER PROGRAMMING APPLICATIONS IN EGR TECHNOLOGY

IET-198 COMP PRGM APP IN ENG TECH.. 00/SP B 1.33

Re: Numeric value in a string

LarryWorley — Wed, 31 Oct 2012 18:51:31 GMT

Nick,

Check out the anydigit string function is sas. To get it to work, you might need to use other functions to check what you need. For example, if you expect the number in the last column of the input string, this would work:

if anydigit(in_string,length(in_string)) > 0 then do whatever:

This works in 9.2 and 9.3. Here is link: http://support.sas.com/documentation/cdl/en/lefunctionsref/63354/HTML/default/viewer.htm#p0gq8bwobt4wjgn12km94wi66mxw.htm

Larry

Re: Numeric value in a string

PGStats — Wed, 31 Oct 2012 19:06:57 GMT

Use scan(x,-1) to get the last field and anyalpha to detect non-numbers :

data have;

length line1 line2 $128;

infile datalines truncover;

input line1 char. / line2 char.;

if not anyalpha(scan(line2,-1));

datalines;

C) D.C.CIRCUITS

EER-126 D.C. CIRCUITS.............. 98/WI A 2.67

N) FUNDAMENTAL MECHANICAL SYSTEMS

EGR-100 FUNDMNTL MECHNCL SKILLS______________________ 1 course needed

C) ROBOTICS IN CIM SYSTEMS

EGR-128 ROBOTICS IN CIM SYSTEMS.... 99/SP A 2

C) COMPUTER PROGRAMMING APPLICATIONS IN EGR TECHNOLOGY

IET-198 COMP PRGM APP IN ENG TECH.. 00/SP B 1.33

;

Re: Numeric value in a string

Astounding — Wed, 31 Oct 2012 20:26:00 GMT

I like the use of scan and -1 to get the last word. But the ANYthis and ANYthat functions are tricky to determine if they have accounted for all the possibilities that might be numeric. Should -.5 be numeric? How about scientific notation, such as 314E-2? Should #5 be character? If the answer is Yes, here is another way to examine the last word:

if input(scan(line2,-1), ??16.) > .;

It examines the first 16 characters of the last word, and tries to read them to a number. If it can, the IF condition is true. If it can't, the ?? will suppress messages about invalid numeric data (and the IF condition will be false). And if the last word is shorter than 16 characters, that doesn't present a problem.

Re: Numeric value in a string

PGStats — Wed, 31 Oct 2012 20:44:56 GMT

That's indeed better. Better still : change the > . to not missing().

Cheers!

Re: Numeric value in a string

art297 — Wed, 31 Oct 2012 22:01:46 GMT

Did you really want to remove the entire line that has the underscores in it, or just that portion from the underscore onward?

Similarly, since all of your other lines end with a number, I would approach the problem slightly differently than my colleagues. Would the following work for you?:

data have (drop=_:);

length line1 line2 $128;

infile datalines truncover;

input line1 char. / line2 char.;

if find(line2,"_") then line2=substr(line2,1,find(line2,"_")-1);

else do;

call scan(line2,-1,_position,_length," ");

line2=strip(substr(line2,1,_position-1));

end;

datalines;

C) D.C.CIRCUITS

EER-126 D.C. CIRCUITS.............. 98/WI A 2.67

N) FUNDAMENTAL MECHANICAL SYSTEMS

EGR-100 FUNDMNTL MECHNCL SKILLS______________________ 1 course needed

C) ROBOTICS IN CIM SYSTEMS

EGR-128 ROBOTICS IN CIM SYSTEMS.... 99/SP A 2

C) COMPUTER PROGRAMMING APPLICATIONS IN EGR TECHNOLOGY

IET-198 COMP PRGM APP IN ENG TECH.. 00/SP B 1.33

;

Re: Numeric value in a string

Haikuo — Wed, 31 Oct 2012 23:50:17 GMT

Echo with Astounding's sentiment, since scan() has bunch of default delimiters, such as . < ! - etc, it is better for us to explicitly spell out the wanted delimiter ' '(blank):

scan(line2,-1, ' ')

so as something like "T-2" or "hai.2" would not be taken as numbers.

Haikuo

Re: Numeric value in a string

PGStats — Thu, 01 Nov 2012 14:55:25 GMT

Further tweaking... short of Regex :

if not missing(input(scan(line2,-1,":=","s"),??16.));

Re: Numeric value in a string

nickb — Thu, 01 Nov 2012 17:19:29 GMT

This worked great. Thanks for the reply.

Nick

Re: Numeric value in a string

Linlin — Thu, 01 Nov 2012 20:50:43 GMT

Hi PG,

I am lazy to look up online. What do

":=","s" do in

scan(line2,-1,":=","s"),??16.)?

Thank you!

Re: Numeric value in a string

PGStats — Thu, 01 Nov 2012 21:27:46 GMT

It adds the characters '=' and ':' to spacing characters (space, tab, etc.) as valid SCAN separators. I figured colon and equal characters could appear before a number.

Re: Numeric value in a string

Linlin — Thu, 01 Nov 2012 21:41:02 GMT

Thank you!

When you say spacing characters do you mean "delimiters"?

Would you please show me an example?:smileysilly:

Re: Numeric value in a string

PGStats — Thu, 01 Nov 2012 23:47:19 GMT

Otherwise called word dividers, interword separators, whitespace... From the SCAN documentation :

s or S adds space characters to the list of characters (blank, horizontal tab, vertical tab, carriage return, line feed, and form feed).

So that lines ending with :

A 125

A=125

A:125

A 125

will be treated appropriately.

Re: Numeric value in a string

Linlin — Fri, 02 Nov 2012 00:03:07 GMT

Thank you PG!

After running the code below, I know why you added ":=","s". Sometime my mind doesn't work at all.

data have1;

input line2 $1-20;

If not missing(input(scan(line2,-1,":=","s"),??16.));

cards;

asdfgfhrfren 1

vdsjwheffffffff

dnfejfudsdhjn:2

vfdgriegrgggg=3

;

proc print;run;

data have2;

input line2 $1-20;

If not missing(input(scan(line2,-1),??16.));

cards;

asdfgfhrfren 1

vdsjwheffffffff

dnfejfudsdhjn:2

vfdgriegrgggg=3

;

proc print;run;

Re: Numeric value in a string

robertrao — Fri, 02 Nov 2012 19:54:28 GMT

Hi,

Could you please explain me this concept?

"It examines the first 16 characters of the last word"

what is the meaning of last word??

in the example below what if I have more than 16 letters in the word???

asdfgfhrfren 1

vdsjwheffffffff

dnfejfudsdhjn:2

vfdgriegrgggg=3

Thanks

Re: Numeric value in a string

art297 — Fri, 02 Nov 2012 20:04:33 GMT

I think that was a slight misstatement. The code you are referring to takes the last word from the line that was input and tries to input it as an up-to-16 digit number.

Re: Numeric value in a string

robertrao — Fri, 02 Nov 2012 20:16:03 GMT

Hi,

Thanks a lot.

What would it do for this particular case??

kkkkkkkkkkkgggggghhhhhdnfejfudsdhjn:2 (more than 16 letters)

Regards

Re: Numeric value in a string

art297 — Fri, 02 Nov 2012 20:23:07 GMT

using PGStat's code it would select 2 and read it as a number since it was preceded by a :.

The other versions would drop the record since the last word isn't a number.

Re: Numeric value in a string

nickb — Tue, 20 Nov 2012 15:12:37 GMT

I thought this solution would work until I found a few records that are being omitted. This is a line that I want.

INT-270 INT INTERNSHIP............. 09/SUB A

3.33 *F

Re: Numeric value in a string

nickb — Tue, 20 Nov 2012 15:19:07 GMT

In doing some troubleshooting, I found out I was missing some data and below is the reason why. I need the row and the *F is the problem.

Things that I know about the file:

1) The first hyphen is at position 14.

2) A number will be at position 15.

What i have for the IF condition

if index(row, '-')= 14 and input(scan(row,-1), ??16.) >. and ANYDIGIT(row)= 15

INT-270 INT INTERNSHIP............. 09/SUB A

3.33 *F