Learning SAS? Welcome to the exclusive online community for all SAS learners.

Why cant I import an excel file after creating my library? What is the location of library? How to upload? Please answer!! Help me out!!

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

Why cant I import an excel file after creating my library? What is the location of library? How to upload? Please answer!! Help me out!!

MY CODE:

import datafile = 'C:/SASUniversityEdition/myfolders/sasuser.v94/myfolder/newdata1.xlsx';

DBMS = excel out= SASout;

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

42 ;

43 proc import datafile = 'C:/SASUniversityEdition/myfolders/sasuser.v94/myfolder/newdata1.xlsx';

ERROR: Output SAS data set must be provided.

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

  

44 DBMS = excel out= SASout;

45 ;

46 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

56 ;

what is wrong now??


Accepted Solutions
Solution
‎06-11-2014 01:12 PM
SAS Super FREQ
Posts: 8,743

Re: Why cant I import an excel file after creating my library? What is the location of library? How to upload? Please answer!! Help me out!!

Hi, Also, to elaborate on the previous response, when you use the SAS University Edition, you are using a Linux-based image and you might not be able to refer to your files with a Windows directory location. For example, this code works for me using VMware Player as my virtualization software and the University Edition. Note that my program shows both an EXPORT (from SASHELP.CLASS to an Excel file) and then also shows an IMPORT (of the just-created file into a temporary SAS dataset #2 and then a permanent SAS dataset #3). You will NOT be able to run the code unless you change the folder locations to use your folder locations. Also step 2 and step 3 cannot be run unless you run step 1 first.

  I hope this helps. If you have additional problems with importing Excel data, you might want to open a track with Tech Support for more direct help.

Cynthia

** 1) Make an Excel file from SASHELP.CLASS;
PROC EXPORT DATA=sashelp.class
            OUTFILE="/folders/myfolders/all_excel/newclass.xlsx"
            DBMS=xlsx REPLACE;
     SHEET="Mysheet";
RUN;
   
** 2) Import file that was just created into a different dataset;
PROC IMPORT OUT=work.myclass
            DATAFILE="/folders/myfolders/all_excel/newclass.xlsx"
            DBMS=xlsx REPLACE;
     SHEET="Mysheet";
run;
 
proc print data=work.myclass;
title 'Data Imported from newclass.xlsx';
run;
title;

   

libname perm '/folders/myfolders/all_SAS_data';
** 3) import data into permanent SAS library;
PROC IMPORT OUT=perm.final
            DATAFILE="/folders/myfolders/all_excel/newclass.xlsx"
            DBMS=xlsx REPLACE;
     SHEET="Mysheet";
run;
 
proc print data=perm.final;
title 'Data Imported from newclass.xlsx';
title2 "Into a library named PERM located in SAS Studio path: %sysfunc(pathname(PERM))";
run;
title;


proc_import_example_SAS_Studio_Univ_Edition.pngcompare_windows_path_with_UE_path.pngshow_import_export_with_SAS_Studio_and_Univ_Edition.png

View solution in original post


All Replies
Super User
Posts: 10,521

Re: Why cant I import an excel file after creating my library? What is the location of library? How to upload? Please answer!! Help me out!!

The output data set must be part of the proc import statement. Since you placed a ; after the end of the datafile you ended the proc import statement before including the output dataset.

Solution
‎06-11-2014 01:12 PM
SAS Super FREQ
Posts: 8,743

Re: Why cant I import an excel file after creating my library? What is the location of library? How to upload? Please answer!! Help me out!!

Hi, Also, to elaborate on the previous response, when you use the SAS University Edition, you are using a Linux-based image and you might not be able to refer to your files with a Windows directory location. For example, this code works for me using VMware Player as my virtualization software and the University Edition. Note that my program shows both an EXPORT (from SASHELP.CLASS to an Excel file) and then also shows an IMPORT (of the just-created file into a temporary SAS dataset #2 and then a permanent SAS dataset #3). You will NOT be able to run the code unless you change the folder locations to use your folder locations. Also step 2 and step 3 cannot be run unless you run step 1 first.

  I hope this helps. If you have additional problems with importing Excel data, you might want to open a track with Tech Support for more direct help.

Cynthia

** 1) Make an Excel file from SASHELP.CLASS;
PROC EXPORT DATA=sashelp.class
            OUTFILE="/folders/myfolders/all_excel/newclass.xlsx"
            DBMS=xlsx REPLACE;
     SHEET="Mysheet";
RUN;
   
** 2) Import file that was just created into a different dataset;
PROC IMPORT OUT=work.myclass
            DATAFILE="/folders/myfolders/all_excel/newclass.xlsx"
            DBMS=xlsx REPLACE;
     SHEET="Mysheet";
run;
 
proc print data=work.myclass;
title 'Data Imported from newclass.xlsx';
run;
title;

   

libname perm '/folders/myfolders/all_SAS_data';
** 3) import data into permanent SAS library;
PROC IMPORT OUT=perm.final
            DATAFILE="/folders/myfolders/all_excel/newclass.xlsx"
            DBMS=xlsx REPLACE;
     SHEET="Mysheet";
run;
 
proc print data=perm.final;
title 'Data Imported from newclass.xlsx';
title2 "Into a library named PERM located in SAS Studio path: %sysfunc(pathname(PERM))";
run;
title;


proc_import_example_SAS_Studio_Univ_Edition.pngcompare_windows_path_with_UE_path.pngshow_import_export_with_SAS_Studio_and_Univ_Edition.png
N/A
Posts: 1

Re: Why cant I import an excel file after creating my library? What is the location of library? How to upload? Please answer!! Help me out!!

This was extremely helpful in solving my problems! Thank you so much!

N/A
Posts: 1

Re: Why cant I import an excel file after creating my library? What is the location of library? How to upload? Please answer!! Help me out!!

Hi Cynthia, thank you for your solution above. i am having the same problem. i tried using the unix reference you explained above but got the same error. the file i am trying to import is saved in a folder on my desktop. do i need to changed the location? please advise. Thanks!

the file location is: C:\Users\Owner\Desktop\SASUniversityEdition\myfolders\all_excel

ERROR: Physical file does not exist, /folders/myfolders/all_excel/hotdog.xls.



☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 6102 views
  • 5 likes
  • 5 in conversation