topic Re: I need to import the attached .xls file in SAS Procedures

I need to import the attached .xls file

mahler_ji — Fri, 12 Sep 2014 16:47:02 GMT

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

Re: I need to import the attached .xls file

Tom — Fri, 12 Sep 2014 17:37:19 GMT

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.

Re: I need to import the attached .xls file

mahler_ji — Fri, 12 Sep 2014 18:50:34 GMT

@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.

Re: I need to import the attached .xls file

Tom — Fri, 12 Sep 2014 18:57:25 GMT

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.

Re: I need to import the attached .xls file

Reeza — Fri, 12 Sep 2014 19:07:21 GMT

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.

Re: I need to import the attached .xls file

mahler_ji — Fri, 12 Sep 2014 19:08:01 GMT

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

Re: I need to import the attached .xls file

mahler_ji — Fri, 12 Sep 2014 19:11:08 GMT

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

Re: I need to import the attached .xls file

mahler_ji — Fri, 12 Sep 2014 19:13:10 GMT

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;