I need to import the attached .xls file

[mahler_ji]

Hello,

I am trying to import the attached file into SAS.  I am using the following code:

data per.a1;

infile "/home/mit/mahler/mahler/Conf_Call_Hist_*.xls" dlm=',' DSD missover lrecl=32767 firstobs=2;

informat companyid $12. ;

informat ticker $6. ;

informat company_name $44. ;

informat quarter $4. ;

informat fiscal_year $7. ;

informat date $12.;

format companyid $12.;

format ticker $6. ;

format company_name $44. ;

format quarter $4. ;

format fiscal_year $7. ;

format date $12.;

input

companyid  $

ticker $

company_name $

quarter $

fiscal_year $

date $ ;

run;

Where I have already assigned the library "per"

I am getting a dataset to come out but it is just a bunch of symbols.

Any help would be great.

John

[Tom]

The most obvious problem is that your program is reading a CSV file and you posted an XLS file.  XLS is a binary format that Microsoft uses for Excel spreadsheets.  CSV is a plain text with one line per observation and commas between the variables.  If you open the XLS file in Excel you can try to save it as a CSV file and then perhaps your SAS program will work.

[mahler_ji]

@tom

I am sorry, that was just because I pasted the latest version of my code and a less recent version of the spreadsheet.  I have tried saving the file as a .csv file and as an .xls file and then called the same thing in the infile statement.  Neither has worked.

I always make sure that they match. 

When I run the above code with an .xls file, using "," as my delimiter, I get a file out with observations and variables, but they are symbols and most of the variables are blank.

Any help would be great.

[Tom]

Please re-read what I wrote.  It should fully explain why you are seeing strange characters instead of normal alphanumeric characters.

If you want SAS to read an actual XLS file then you will need to use PROC IMPORT.

[mahler_ji]

When I save it as a .csv file and try to use the same code above, instead calling the .csv version of the file, I get a dataset with 0 observations.  Is there some other way that I am supposed to be converting this to a .csv file other than just saving it as a .csv file?

I have also tried to use proc import using the following code, or some variation of it:

proc import datafile="/home/mit/mahler/mahler/Conf_Call_Hist_*.csv"

     out=per.a1

     dbms=csv

     replace;

     getnames= yes;

run;

I have no idea why this is not working...  I have imported these files before without any problem.

Thanks, Tom

[Reeza]

You can't read an XLS file with a data step infile statement, you need a proc import.

You can read a CSV file with a data infile statement.

[mahler_ji]

Thank you very much for your reply.  I have tried to save it as a .csv file and rerun the code, and simply changing the infile statement, but that doesn't work either.

I have also tried to use proc import, as seen in the comment above, using both the .xls and the .csv form of the file.

Thanks for your help!

John

[mahler_ji · Accepted solution]

Ok guys, thanks for your help!

Just figured it out with the following code

proc import datafile="/home/mit/mahler/mahler/Conf_Call_Hist_*.xls"

     out=per.a2

     dbms=xls

     replace;

     getnames= yes;

run;