Help using Base SAS procedures

I need to import the attached .xls file

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 101
Accepted Solution

I need to import the attached .xls file

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


Accepted Solutions
Solution
‎09-12-2014 03:13 PM
Frequent Contributor
Posts: 101

Re: I need to import the attached .xls file

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;

View solution in original post


All Replies
Super User
Super User
Posts: 6,144

Re: I need to import the attached .xls file

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.

Frequent Contributor
Posts: 101

Re: I need to import the attached .xls file

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

Super User
Super User
Posts: 6,144

Re: I need to import the attached .xls file

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.

Frequent Contributor
Posts: 101

Re: I need to import the attached .xls file

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

Grand Advisor
Posts: 16,905

Re: I need to import the attached .xls file

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.

Frequent Contributor
Posts: 101

Re: I need to import the attached .xls file

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

Solution
‎09-12-2014 03:13 PM
Frequent Contributor
Posts: 101

Re: I need to import the attached .xls file

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;

☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 440 views
  • 3 likes
  • 3 in conversation