BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mahler_ji
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
mahler_ji
Obsidian | Level 7

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

7 REPLIES 7
Tom
Super User Tom
Super User

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
Obsidian | Level 7

@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
Super User Tom
Super User

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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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
Obsidian | Level 7

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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