import from excel and save it to a permanent library

[ralphbenno]

libname new 'd:\SAS-library';
input sal @@;
cards;
0.593 0.142 0.329 0.691 0.231
0.793 0.519 0.392 0.418
run;

proc print data=new.sal;
run;

error : libname new is not assigned error

this was a test code.. but I wanted to bring excel data into a library.

help||||

[VDD]

options validvarname=v7;
libname NP xlsx  "/data/np_info.xlsx";
proc contents data=np.parks;
run;
libname np clear;

[ralphbenno]

it did not work

here is my code

data sales;
proc import datafile="d:data.xlsx"
dbms=excel
out=region;
run;
proc print
run;

I got an error= device offline

and my data look like

c	g	m	p	n	s
90108	57770	53059	30644	74770	68813
94928	63935	57789	33871	91463	76651
84789	53530	50264	28114	74082	67097
75063	49427	43459	25980	59728	63147

[VDD]

what is incorrect in this libname statement you have supplied

"d:data.xlsx"

where are the \/'s

would this be a more correct path "d:\data.xlsx"

create a libname to the excel information.

libname myexcel xlsx "d:\";

you will then have access to all xlsx files in that libname.

[Tom]

---

@VDD wrote:

...

libname myexcel xlsx "d:\";

you will then have access to all xlsx files in that libname.

---

That is not how the XLSX engine works. You need to give it the path of a single XLSX workbook file. Not a directory. 

Then you have access to all of the SHEETS in that single WORKBOOK.

[andreas_lds]

How do you import an excel file?

libname e xslx "path-and-filename-with-extension";
libname p "existing-directory";

proc copy in=e out=p;
/* Maybe use SELECT to choose the tables to be copied */
run;

[andreas_lds]

You are mixing data step an proc import, there should be errors in the log.

The following step should create a temporary dataset:

proc import datafile="d:\data.xlsx"
dbms=xlsx
out=region;
run;

If you get any errors, post the log using the {i} button.

[ralphbenno]

I tried your fix and still getting an error

NOTE: Copyright (c) 2002-2012 by SAS Institute Inc., Cary, NC, USA.
NOTE: SAS (r) Proprietary Software 9.4 (TS1M3)
      Licensed to UNIVERSITY OF WEST FLORIDA - SFA T&R, Site 70129736.
NOTE: This session is executing on the X64_8PRO  platform.

NOTE: Updated analytical products:

      SAS/STAT 14.1
      SAS/ETS 14.1
      SAS/OR 14.1
      SAS/IML 14.1
      SAS/QC 14.1

NOTE: Additional host information:

 X64_8PRO WIN 6.2.9200  Workstation

NOTE: SAS initialization used:
      real time           3.71 seconds
      cpu time            1.46 seconds

1    proc import datafile="d:\data.xlsx"
2    dbms=xlsx
3    out=region;
4    run;

ERROR: Error opening XLSX file -> d:\\data.xlsx .  It is either not an Excel spreadsheet or it is
damaged.   Error code=80000808
Requested Input File Is Invalid
ERROR: Import unsuccessful.  See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds

please explain again

thks

[Tom]

SAS is saying that your file with a name that ends in .XLSX is not, in fact, an XLSX file.

Try opening it in Excel and saving it as a new file and make sure to get the filetype when you save it.

[Reeza · Accepted solution]

If you ran some of the other code, there's a change you've destroyed your file because you essentially overwrote it. If you can, restore it and ensure it's correct and then try the code you've posted again.