DATA Step, Macro, Functions and more

Problem with Proc Import - Work arounds?

Reply
Occasional Contributor
Posts: 6

Problem with Proc Import - Work arounds?

Hi all.

I am having a horrific issue with Proc Import. I was using it to read in a rather messy file in version 9.1.3. It took a bit of cleaning up but the data read in as the correct format and once it came into a sas dataset it could be cleaned a bit into a useful set of data for us.

We recently upgraded to version 9.2 and we completely lost our ability to do what we have been doing in 9.1 for over a year. Now the data reads in as text when before it read in as numeric. Here is a brief sample of the data coming from excel.

F1 F2 F3 F4 F5 F6

product region plan Jan-2010 Feb-2010 Mar-2010
external
plans vs region
First quarter 2010

COMM CHI ARKANSAS 23,485 12,347 17,263
COMM SF CALIFORNIA 825 734 812
COMM SF PORTLAND 1,235 2,345 14,235
COMM NY WESTCHESTER 127,345 112,712 105,399

All I really care about is capturing the last four lines. I don't care about the dates or the other extraneous data in the file including the dates. I just want a dataset with the data from the last four lines. When using the proc import statement in 9.1 it worked fine in terms of reading the actual numeric data. It converted that comma-formatted data in Excel to numeric variables. Then I could use a little programming logic to clean up the file and get rid of those extraneous rows of data so all that was left were the last four and the data themselves (variables F4-F6) were formatted as numeric varialbes.

But in 9.2 the Proc Import is suddenly making all those variables character. And I can't seem to convert them back into numerics.

Why did Proc Import change between versions. Any thoughts on what to do with this so I can once again have numeric variables?

Thanks in advance.
Valued Guide
Posts: 634

Re: Problem with Proc Import - Work arounds?

I cannot address the differences between 9.1.3 and 9.2 for PROC IMPORT, but given that you are getting character values, have you tried using the COMMA. format with an INPUT function?
Super User
Posts: 11,343

Re: Problem with Proc Import - Work arounds?

Generally I take the code that PROC IMPORT generates, as it appears in the log, and modify it. Copy the code out of the log, paste into the editor and clean up by changing INFORMATS, which are what are setting character instead of numeric, and the INPUT for the offending variables to remove the $ I expect you'll find.

And the keep that program around instead of proc import.
Super User
Posts: 10,020

Re: Problem with Proc Import - Work arounds?

Did you try
[pre]

MIXED=NO;
USEDATE=NO;
[/pre]



Ksharp
Ask a Question
Discussion stats
  • 3 replies
  • 156 views
  • 0 likes
  • 4 in conversation