## Reading in data formatted differently.

Solved
Occasional Contributor
Posts: 5

# Reading in data formatted differently.

Hello, I am a student working on a project and I have come across a problem that I can't solve.

I am reading in data that deals with colleges and one of the variables is a ratio.

The problem is that the ratios were entered in two different ways, sometimes they were entered in the standard 'X : Y' format and other times they were simply entered as a decimal such as 1.75.

Is there a way to create an informat that will be able to read in the ratios along with the decimals? Thanks.

Example:

 2.159027778 1.790277778 33 : 67 2.240972222 1.954166667 2.56875 0.602083333 0.847916667 39 : 61

Accepted Solutions
Solution
‎12-08-2016 10:50 AM
Posts: 1,837

## Re: Reading in data formatted differently.

[ Edited ]

You can overcome the situation by reading the ratio in an alphanumeric temporary variable

defined with informat \$12, then code:

if indexc(temp_var,':')

then ratio = input(scan(temp_var,1,':'),best3.) / input(scan(temp_var,2,':'),best3.);

else ratio = input(temp_var, best12.9 );

All Replies
Super User
Posts: 13,563

## Re: Reading in data formatted differently.

As pasted there is some other character between the numerals and the : . Is that the actual case in your data? If so what is the character as you see it? Sometimes things pasted into the forum, especially from a non-plain text source such as EXCEL, acquire additional "stuff".

If this is a ratio such as 39:61 I would suspect that you want the division of 39/61 done, correct?

Occasional Contributor
Posts: 5

## Re: Reading in data formatted differently.

I pasted from excel but I don't see anything else besides the decimal and the colon between numbers?

Yes that would be the end goal

Super User
Posts: 13,563

## Re: Reading in data formatted differently.

It may be my work browser, Internet Explorer, but just looking at the pasted values there is a space between the colon and the digits on each side.

Copied and paste from the first post:

39 : 61

Typed:

39:61

Solution
‎12-08-2016 10:50 AM
Posts: 1,837

## Re: Reading in data formatted differently.

[ Edited ]

You can overcome the situation by reading the ratio in an alphanumeric temporary variable

defined with informat \$12, then code:

if indexc(temp_var,':')

then ratio = input(scan(temp_var,1,':'),best3.) / input(scan(temp_var,2,':'),best3.);

else ratio = input(temp_var, best12.9 );

Occasional Contributor
Posts: 5

## Re: Reading in data formatted differently.

Thanks! It worked

☑ This topic is solved.