topic Import the excel to SAS dataset in New SAS User

Import the excel to SAS dataset

Smitha9 — Tue, 13 Sep 2022 20:40:07 GMT

I have an excel file and needs to import into SAS. Somehow this is not working.

any tricks here?

proc import datafile="folder\file.xlsx"
dbms=xlsx replace
out=a;
guessingrows=3000;/*guessingrows helps not to truncate the observations*/
run;

I am doing something wrong here? the log says "DBMS type xlsx is not valid to import"

thanks in advance.

Re: Import the excel to SAS dataset

Reeza — Tue, 13 Sep 2022 20:44:14 GMT

Please post your full code and log.

@Smitha9 wrote:

I have an excel file and needs to import into SAS. Somehow this is not working.

any tricks here?

proc import datafile="folder\file.xlsx"
dbms=xlsx replace
out=a;
guessingrows=3000;/*guessingrows helps not to truncate the observations*/
run;

I am doing something wrong here? the log says "DBMS type xlsx is not valid to import"

thanks in advance.

Re: Import the excel to SAS dataset

ballardw — Tue, 13 Sep 2022 21:44:57 GMT

For Import to work with XLSX files you need to have the write module licensed.

If you run

proc setinit;
run;

and do not see something like

---SAS/ACCESS Interface to PC Files
      30MAY2023

then your license does not include the access to XLSX files with Import. Solution: Use File-Save as menu to save to a CSV file and import that.

If you do see the Interface to PC Files then run this to see if it was installed.

proc product_status;run;

If the log does not show, among other products, something like the following then the it was not installed and you need to do a reinstall and make sure the PC Files is selected

For SAS/ACCESS Interface to PC Files ...
   Custom version information: 9.4_M4

"Not working" is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "</>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "</>" icon or attached as text to show exactly what you have and that we can test code against.