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
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;
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.
@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.
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.
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
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.
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
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.