topic Re: import from excel and save it to a permanent library in SAS Programming

import from excel and save it to a permanent library

ralphbenno — Sun, 24 Mar 2019 14:26:46 GMT

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

Re: import from excel and save it to a permanent library

VDD — Sun, 24 Mar 2019 15:14:17 GMT

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

Re: import from excel and save it to a permanent library

andreas_lds — Sun, 24 Mar 2019 16:08:42 GMT

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;

Re: import from excel and save it to a permanent library

ralphbenno — Sun, 24 Mar 2019 17:14:44 GMT

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

Re: import from excel and save it to a permanent library

VDD — Sun, 24 Mar 2019 17:23:03 GMT

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.

Re: import from excel and save it to a permanent library

Tom — Sun, 24 Mar 2019 17:37:17 GMT

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

Re: import from excel and save it to a permanent library

andreas_lds — Sun, 24 Mar 2019 18:52:25 GMT

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.

Re: import from excel and save it to a permanent library

ralphbenno — Sun, 24 Mar 2019 19:22:33 GMT

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

Re: import from excel and save it to a permanent library

Tom — Sun, 24 Mar 2019 20:41:16 GMT

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.

Re: import from excel and save it to a permanent library

Reeza — Sun, 24 Mar 2019 23:11:45 GMT

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.