SAS import open Excel file

[hhchenfx]

Hi Everyone,

I have SAS9.4 (I think it is Base) and I would like to import an open file of either xlsx, csv or txt or any format that allow me to do so.

Can you please help?

Thanks,

HHC

[Tom · Accepted solution]

---

@hhchenfx wrote:

Hi Everyone,

I have SAS9.4 (I think it is Base) and I would like to import an open file of either xlsx, csv or txt or any format that allow me to do so.

Can you please help?

Thanks,

HHC

---

No idea what "open file" means here so I will ignore that.  If is important then explain what it means.

Assuming you mean you want to get the contents of the Excel file into SAS dataset(s) what you can do depends on whether or not you have ACCESS to PC FILES licensed.  That includes the drivers to read Excel files directly.

So if you have an XLSX file you can treat it as it was a SAS library by using a LIBNAME statement with the XLSX engine.

So this code will copy all of the sheets in the XLSX file name din the LIBNAME statement into datasets in the WORK library.

libname myfile xlsx 'filename.xlsx';
proc copy inlib=myfile out=work;
run;

If you do not have SAS Access to PC FILES then the best thing to do is save the individual sheets in the Excel file into individual CSV files.  Make sure that none of the cells have embedded line breaks as that can cause creation of file SAS cannot read .

You can read a CSV file directly with a data step using code like this:

data want;
  infile 'myfile.csv' dsd truncover firstobs=2;
  length var1 8 var2 $10 datevar 8 ..... lastvar $50 ;
  informat datevar date9.;
  format datevar date9.;
  input var1 -- lastvar;
run;

If you want to let SAS GUESS what variables to create you can use PROC IMPORT.

proc import file='myfile.csv' dbms=csv out=want replace;
  guessingrows=max;
run;

But be prepared for it doing stupid things.

[SASKiwi]

If an Excel workbook is already open, SAS will not be able to read it at all as Excel locks the file for editing. If you have a CSV file open in Excel I think you would have the same problem. Why do you want to read such a file while it is open?

[ballardw]

Adding to @SASKiwi's comment. Most programs that have the ability to edit values will lock the file so the edits done there are preserved. So if "open" means "in use by program X" then you need to specify which program that actually may be and why is it required to edit while open in another program. Those that don't edit files may not show any attempt to modify the contents by another program even if not locked. Plus may require a manual "refresh" to show any changes if the program can show them.