Reading in data formatted differently.

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

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
Trusted Advisor
Posts: 1,385

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 );

View solution in original post


All Replies
Super User
Posts: 10,511

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: 10,511

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
Trusted Advisor
Posts: 1,385

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 Smiley Happy

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 201 views
  • 1 like
  • 3 in conversation