Help using Base SAS procedures

Importing a .xlsx file into SAS

Reply
Occasional Contributor
Posts: 6

Importing a .xlsx file into SAS

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.

Super User
Posts: 11,343

Re: Importing a .xlsx file into SAS

Start with proc import.

 

Then Proc contents

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

Occasional Contributor
Posts: 6

Re: Importing a .xlsx file into SAS

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 ...

Trusted Advisor
Posts: 1,586

Re: Importing a .xlsx file into SAS

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

Occasional Contributor
Posts: 6

Re: Importing a .xlsx file into SAS

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

 

 

 

Super User
Super User
Posts: 7,997

Re: Importing a .xlsx file into SAS

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.

Occasional Contributor
Posts: 6

Re: Importing a .xlsx file into SAS

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

Super User
Super User
Posts: 7,997

Re: Importing a .xlsx file into SAS

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/

Ask a Question
Discussion stats
  • 7 replies
  • 1027 views
  • 0 likes
  • 4 in conversation