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

Export dataset from SAS university edition to excel

Accepted Solution Solved
Reply
Occasional Contributor Bo7
Occasional Contributor
Posts: 13
Accepted Solution

Export dataset from SAS university edition to excel

Hi, I'm working on my master thesis and I have to make some statistical calculations on a dataset.

However, I would like to do the statistical analysis in Excel or in Stata, because I have no programming experience whatsoever. So I can't really work with the dataset provided in SAS.

Could someone please explain to me what steps I should take to get my dataset into excel or stata? Beneath you can see what I already tried and the error messages I got when trying...

Could you maybe elaborate on what I have to do, and why exactly I have to do that?

Is it possible that the dataset is too big to export it to an excel file? I also tried to export it into a stata (dta) file, because that is the program I would like to use for my thesis research.

This is the code I tried to run:

proc export data=myfolder.ssbf03 DBMS=xls outfile="users/boclaes/Desktop/thesis.xls";

run;

Schermafbeelding 2015-03-24 om 13.00.42.png

and since I am no programming expert, I have no idea what all these error messages mean.

I tried to export to a stata file, thinking the dataset was too large for an excel file and then I got this error message:

Schermafbeelding 2015-03-24 om 13.07.23.png

Can you see what the problem is based on these error messages?

Thank you so much for your help !


Accepted Solutions
Solution
‎03-24-2015 06:14 PM
Super User
Super User
Posts: 6,502

Re: Export dataset from SAS university edition to excel

Use XLSX format instead of XLS format for the file. 

View solution in original post


All Replies
Community Manager
Posts: 2,764

Re: Export dataset from SAS university edition to excel

Don't give up! You probably do not need to know that much programming to accomplish what you need to in SAS.  You can post specific "How do I" questions in the SAS communities for help.

However, if you must export your file, try this:

proc export data=myfolder.ssbf03 DBMS=xlsx

   outfile="/folders/myfolders/thesis.xlsx";

run;

Chris

Occasional Contributor Bo7
Occasional Contributor
Posts: 13

Re: Export dataset from SAS university edition to excel

I still get the same error message, but maybe it has something to do with the fact that it is called myfolder on my computer instead of myfolders, so without the "s". Because when I tried to make the folder I got an error message that there were too many characters as you can see here:

Schermafbeelding 2015-03-23 om 22.20.16.png

In the SAS information center, I get this message :

Schermafbeelding 2015-03-24 om 15.11.08.png

Could any of this have anything to do with the problem?

Thanks in advance!

SAS Super FREQ
Posts: 8,743

Re: Export dataset from SAS university edition to excel

Hi:

  The message has nothing to do with the size of your file. You cannot use DBMS=EXCEL in SAS University Edition or SAS OnDemand. However, the code in your post shows DBMS=XLS (which should work) but your screen shot shows DBMS=EXCEL, which are 2 different engines. One should work, the other will not.

     

  Also, your physical file name is incorrect. You cannot use your "local" computer location in your file reference. For example, if you are using SAS University Edition, then you would need something like this:

       

proc export data=myfolder.ssbf03 DBMS=xls

     outfile="/folders/myfolders/thesis.xls";

run;

   

proc export data=myfolder.ssbf03 DBMS=xlsx

     outfile="/folders/myfolders/thesis.xlsx";

run;
     

proc export data=myfolder.ssbf03 DBMS=dta

     outfile="/folders/myfolders/thesis.dta";

run;

  So, I am not sure whether the DTA will work, but one of the other 2 should work. Note that the DBMS value matches the file extension of the file you are creating.  If you are using SAS OnDemand for Academics on the server with SAS Studio, then the physical location would be the name of the folder you received in your OnDemand instructions. It should be something like: outfile="/home/<yourusername>/thesis.xls"

cynthia

Occasional Contributor Bo7
Occasional Contributor
Posts: 13

Re: Export dataset from SAS university edition to excel

I still get the same error message, but maybe it has something to do with the fact that it is called myfolder on my computer instead of myfolders, so without the "s". Because when I tried to make the folder I got an error message that there were too many characters as you can see here:

Schermafbeelding 2015-03-23 om 22.20.16.png

In the SAS information center, I get this message :

Schermafbeelding 2015-03-24 om 15.11.08.png

Could any of this have anything to do with the problem?

Thanks in advance!

Community Manager
Posts: 2,764

Re: Export dataset from SAS university edition to excel

Breaking this down a bit...

The first MYFOLDER reference is a SAS library, constrained to no more than 8 characters.  A SAS library is like an alias -- another name that maps to a physical destination such as a folder/directory or a database.  In this case, it maps to the /folders/myfolders path that you mapped in your VM environment.

The second reference -- in the OUTFILE option -- is to that path.  Two ways that SAS uses to reference the same physical location.  It can be confusing at first, but the SAS library concept is powerful -- it allows you to craft SAS programs that work in multiple environments without having to change the program every time your source data location changes.

These concepts are covered in the free SAS training that you can work through.

proc export

  data=myfolder.ssbf03 /* MYFOLDER is a SAS library, not a folder */

  DBMS=xlsx

  outfile="/folders/myfolders/thesis.xlsx"; /* output destination is a folder that maps to a Shared Folder you have in the VM environment */

run;


Chris

Occasional Contributor Bo7
Occasional Contributor
Posts: 13

Re: Export dataset from SAS university edition to excel

Hi Chris,

Thank you for your reply.

I try to understand where de libref stands for but when I'm running the proces again I still get errors.

Since my shared folder is a submap of the map 'SasUniversityEdition' (as is required in the Sas University documentation) which is on my desktop, I used these steps to define the outfile.

proc export data=myfolder.ssbf03 DBMS=xlsx

     outfile="/Users/boclaes/Desktop/SasUniversityEdition/myfolders/thesis.xlsx";

run;

Unfortunately I still get an error message as you can see in the screenshot. Schermafbeelding 2015-03-24 om 17.16.12.png

I hope I'll find a solution soon and I really appreciate your help.

Super User
Posts: 10,516

Re: Export dataset from SAS university edition to excel

Basically due to the virtual environment that UE runs in it does not see anything above that myfolders location. All of the data and destinations must be in that folder or a folder subordinate to myfolders

Super User
Super User
Posts: 6,502

Re: Export dataset from SAS university edition to excel

You are still trying to reference the output file as if the SAS program was running directly on your machine. It is not. It is running inside of the virtual machine. So in your SAS code you need to use the folder names as they appear to the virtual machine.

If you LIBREF of MYFOLDER is working then you can use a SAS function to find the path that it points to in order to write the XLSX file to the same folder.

proc export data=myfolder.ssbf03 DBMS=xlsx

     outfile="%SYSFUNC(PATHNAME(myfolder))/thesis.xlsx";

run;

Super User
Posts: 17,868

Re: Export dataset from SAS university edition to excel

Your VM has mapped to the folder set up as myfolders and you can use the shortcut path of /folders/myfolders to reference the path instead of the full path.

proc export data=myfolder.ssbf03 DBMS=xlsx

     outfile="/folders/myfolders/thesis.xlsx";

run;

Occasional Contributor Bo7
Occasional Contributor
Posts: 13

Re: Export dataset from SAS university edition to excel

Thanks a lot for your help, I think I've found the right code.

But now I've got the error message that my dataset contains more than 256 columns.

Is there a way to avoid this problem?

Solution
‎03-24-2015 06:14 PM
Super User
Super User
Posts: 6,502

Re: Export dataset from SAS university edition to excel

Use XLSX format instead of XLS format for the file. 

Occasional Contributor Bo7
Occasional Contributor
Posts: 13

Re: Export dataset from SAS university edition to excel

I've finally have the dataset which I need for my master thesis.

Thank you so much for all your help, I really appreciate all your comments!

Community Manager
Posts: 552

Re: Export dataset from SAS university edition to excel

Hi there, I'm glad you were able to get answers quickly in here. To help other users resolve similar issues in a timely manner, would you mark answers above that were helpful or correct? Thank you so much for reporting back your success!

Occasional Learner
Posts: 1

Re: Export dataset from SAS university edition to excel

Hi, your suggestion helped me get this figured out on my mac. Posting this in case it helps someone figure it out faster. 

the code that worked for me is:

 

proc export data=LibReferenceName.FileNamew/oExtension DBMS=dta outfile="/folders/myfolders/FileName.dta";
run;

 

Note: "LibReferenceName.FileNamew/oExtension"means that I used the name that I gave to my /folders/myfolders drive using the libname command, followed by the name of the data file located in my folders. w/o extension meaning that I did not include the file type in the name, for example, .sas7bdat or .dta or .xlsx   etc. 

☑ This topic is SOLVED.

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

Discussion stats
  • 21 replies
  • 7943 views
  • 6 likes
  • 9 in conversation