topic Re: Importing a .xlsx file into SAS in SAS Procedures

Importing a .xlsx file into SAS

gf1996 — Wed, 26 Oct 2016 21:46:17 GMT

Hello guys,

In this assignment I have to do, it says I have to read an xlsx file to SAS, but I'm struggling to find out how to do ...

Could someone help me out ? Thanks.

Here is the Assignment question:

The dataset Accident_x.xls (added to the Assignment 2 folder) contains information on motor vehicle
accidents collected by the US Bureau of Transportation.

a) Read the file into Assign2.Accident_new and review the data descriptor. Print first 100 observations.

Re: Importing a .xlsx file into SAS

ballardw — Wed, 26 Oct 2016 22:10:11 GMT

Start with proc import.

Then Proc contents

Then proc print (hint there are several options to limit the number of records printed)

Re: Importing a .xlsx file into SAS

gf1996 — Wed, 26 Oct 2016 23:00:17 GMT

Hey ballardw, thanks for the advice. I used the Proc Import Statement, but then I get an error message with the following code:

PROC IMPORT OUT= Assign2.Accident_new DATAFILE= "C:\Users\G_FABRE\Documents\445_695\Assignment_2\Accidents_x.xls"
DBMS=excel ;
RUN;

proc print data= (firstobs=1 obs=100);
run;

I don't see where I made a mistake in this code ...

Re: Importing a .xlsx file into SAS

Shmuel — Wed, 26 Oct 2016 23:41:03 GMT

Please post your log too with the error message, beyond your code.

Re: Importing a .xlsx file into SAS

RW9 — Thu, 27 Oct 2016 08:15:34 GMT

Are you using SAS University Edition? If so the path to C: would be invalid as paths are different on that. Need to read the documentation for that. Updated your code as its hard to read with mixed case (being able to read code is far more important than what it actually does):

proc import out=assign2.accident_new 
            datafile="c:\users\g_fabre\documents\445_695\assignment_2\accidents_x.xls"
            dbms=excel;
run;

proc print data=   (firstobs=1 obs=100);  /* Error on this line, nothing in data=______ */
run;

There is one error I can see, highlighted above, you have not specified a dataset after the data= in the proc print. There is a default position in SAS that it uses the last step's data if nothing is supplied, however I would advise never to use such a thing - if you add more code in you would get odd results. Always be explicit and clear in your code. After some years in the industry you will have had to pick up other peoples code, and then you will understand why this is important.

Re: Importing a .xlsx file into SAS

gf1996 — Thu, 27 Oct 2016 19:42:04 GMT

Here is my new modified code for this exercice:

PROC IMPORT OUT= Assign2.Accident_new DATAFILE= "C:\445_695\Course_Data"
DBMS=xlsx;
RUN;

proc print data= (firstobs=1 obs=100);
run;

Here is my log:

1    PROC IMPORT OUT= Assign2.Accident_new DATAFILE= "C:\445_695\Course_Data"
2                DBMS=xlsx;
3    RUN;

ERROR: Library name is not assigned.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.15 seconds
      cpu time            0.01 seconds

Re: Importing a .xlsx file into SAS

gf1996 — Thu, 27 Oct 2016 20:00:09 GMT

Ok thanks, just modified my code. But now I get the following error message once I submit it:

EDITOR:

PROC IMPORT OUT= Assign2.Accident_new DATAFILE= "C:\445_695\Course_Data\Accidents_x"
DBMS=xlsx;
RUN;

proc print data= Assign2.Accident_new (firstobs=1 obs=100);
run;

LOG:

7    PROC IMPORT OUT= Assign2.Accident_new DATAFILE= "C:\445_695\Course_Data\Accidents_x"
8                DBMS=xlsx;
9    RUN;

ERROR: Library name is not assigned.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

What should I do regarding the unassigned library ?

Thanks

Re: Importing a .xlsx file into SAS

RW9 — Fri, 28 Oct 2016 07:59:08 GMT

You need to have a library assignment statement before the import, something like:

libname Assign2 "c:\mypath";

I would suggest you have a look over the SAS introductory courses, and other help documentation as this is probably the first thing you need to learn about using SAS.

http://support.sas.com/training/tutorial/