topic How do I import an excel spreadsheet (microsoft 365 32-bit) into SAS 9.4 in New SAS User

How do I import an excel spreadsheet (microsoft 365 32-bit) into SAS 9.4

ms2370 — Mon, 24 Feb 2020 10:45:44 GMT

Hello,

Please help. I've searched for the right answer to this but everything that I try doesn't work. I'm simply trying to import an excel spreadsheet (.xlsx, microsoft 365, 32-bit) into my SAS (v9.4). I'm quite a novice, so my go-to is the import wizard, but according to the posts I've found, I can't do that with the program versions I have. They recommended downloading the PC files add-on, which would allow me to use the import wizard again, but the SAS Private Java Runtime Environment (64-bit) didn't download and install properly. I then tried saving the excel spreadsheet as a CSV, which worked, but then several of my character variables were cut off (i.e., instead of yes, it reads as ye, or instead of pulmonary, it reads as pulomon). I have about 1607 observations with about 32 variables so going through each one to make sure it wasn't cut off would be a time-consuming process. What is the best way for me to proceed?

Thank you

Re: How do I import an excel spreadsheet (microsoft 365 32-bit) into SAS 9.4

Ksharp — Mon, 24 Feb 2020 12:00:09 GMT

I then tried saving the excel spreadsheet as a CSV, which worked, but then several of my character variables were cut off (i.e., instead of yes, it reads as ye, or instead of pulmonary, it reads as pulomon).

Try guessingrows=max option:

proc import datafile= .......;

guessingrows=max;

run;

Re: How do I import an excel spreadsheet (microsoft 365 32-bit) into SAS 9.4

Tom — Mon, 24 Feb 2020 13:31:56 GMT

Try just reading the XLSX file directly (without the point and click wizard).

Make a libref that points to the file using the XLSX engine. Then you can read the individual sheets.

libname myfile xlsx 'path to file/filename.xlsx';

Or just copy all of them out

proc copy inlib=myfile out=work;
run;

Re: How do I import an excel spreadsheet (microsoft 365 32-bit) into SAS 9.4

ms2370 — Tue, 25 Feb 2020 10:46:33 GMT

What do I do once I create the libref to the XLSX engine? I only have one sheet in the workbook.