topic Re: Reading in data formatted differently. in SAS Programming

Reading in data formatted differently.

Peadymarvin — Wed, 07 Dec 2016 16:32:46 GMT

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

Re: Reading in data formatted differently.

ballardw — Wed, 07 Dec 2016 16:50:03 GMT

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?

Re: Reading in data formatted differently.

Peadymarvin — Wed, 07 Dec 2016 16:54:15 GMT

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

Re: Reading in data formatted differently.

Shmuel — Wed, 07 Dec 2016 16:57:42 GMT

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

Re: Reading in data formatted differently.

ballardw — Wed, 07 Dec 2016 18:27:52 GMT

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

Re: Reading in data formatted differently.

Peadymarvin — Thu, 08 Dec 2016 15:47:12 GMT

Thanks! It worked 🙂