topic Re: Accessing Excel files in SAS University Editiion in SAS Programming

Accessing Excel files in SAS University Editiion

swatipawar — Mon, 29 Feb 2016 20:08:39 GMT

Hi,

I am using SAS University Edition and I am taking SAS essentials course from the SAS website. However, in the tutorials PCfiles engine is used to access MS Excel files, which I cannot use in University edition. Can anyone suggest an alternate way to access Excel files in SAS University Edition? Also want to know if I can still continue with the same tutorial using the University Edition without using PCfiles engine.

Thanks,

Swati

Re: Accessing Excel files in SAS University Editiion

Reeza — Mon, 29 Feb 2016 20:13:59 GMT

You can't use libname methods, but you can use standard proc import methods.

I don't think it makes an impact on the learning besides how to read the data. Once the data is in, it doesn't matter much how you got it there, the steps afterward would be the same.

Re: Accessing Excel files in SAS University Editiion

Cynthia_sas — Mon, 29 Feb 2016 20:22:49 GMT

Hi:
Please carefully review the instructions in the Lesson on reading Excel files. Students are specifically told that they cannot use the LIBNAME engine for Excel and asked to WRITE the code for the practice, but not to SUBMIT the code. We know that there are students on many different interfaces who will not have the right mix of files to use the LIBNAME engine for Excel. The instructions in the class specifically tell you to study the syntax, but not to submit the code.

cynthia

Re: Accessing Excel files in SAS University Editiion

swatipawar — Mon, 29 Feb 2016 20:26:06 GMT

Thanks for your quick reply Reeza. I entered the following code:

/** Import an XLSX file. **/

PROC IMPORT DATAFILE="/folders/myshortcuts/Myfolder/Sales.xlsx"
OUT=WORK.MYEXCEL
DBMS=XLSX
REPLACE;
RUN;
Proc contents data=sales._all_;
run;

data work.salesnew;
run;

PROC PRINT DATA=WORK.salesnew label;
RUN;

However I am getting the error that Libref Sales is not assigned

Thanks,

Swati

Re: Accessing Excel files in SAS University Editiion

Cynthia_sas — Mon, 29 Feb 2016 20:31:37 GMT

Hi,
you did not define a LIBREF for sales in your posted code. You are making WORK.MYEXCEL from the sales.xlsx file import. So, your proc contents should be:

proc contents data=work.myexcel;
run;

Then, you have a DATA step to create work.salesnew, but you do NOT have a SET statement, so you are not really creating anything. I assume you want to create work.salesnew from the work.myexcel file. One option is to replace work.myexcel with work.salesnew in the PROC IMPORT step or the other option is to alter the DATA step program:

data work.salesnew;
set work.myexcel;
run;

then your PROC PRINT will make sense:
proc print data=work.salesnew label;
run;

(It looks like you copied your PROC CONTENTS step from a previous example that uses the LIBNAME engine. And that will not work.)

cynthia

Re: Accessing Excel files in SAS University Editiion

swatipawar — Mon, 29 Feb 2016 20:45:46 GMT

Thank you Cynthia. Basically I am new with SAS and have no idea how Proc Import statement work. I watched one youtube video and there I found this option. Can you please explain how the proc import statement works.
Thanks

Re: Accessing Excel files in SAS University Editiion

Reeza — Mon, 29 Feb 2016 20:57:10 GMT

The first SAS e-course is free, you may want to take that for a step by step walk through of getting started with SAS. It is also designed for SAS UE, so that will help avoid technical difficulties.

Proc import -> imports data. The specifics actually vary depending on the type of data, so its worth reading the documentation on the procdure.

One good thing to learn, is how to navigate the documentation, because a lot of the questions you're going to have will have the answers there.

Re: Accessing Excel files in SAS University Editiion

swatipawar — Mon, 29 Feb 2016 21:01:45 GMT

Thanks Cynthia.