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

Export dataset from SAS university edition to excel

Reply
Occasional Contributor
Posts: 6

Export dataset from SAS university edition to excel

[ Edited ]

Hi,

I have a file in .sas7bdat format that I want to convert to excel. 

The SAS file name is "deals" and it is located in a folder called "Dealscan" within myfolders.

I imported the file to sas using the following commands, but an error message keeps showing:

libname Dealscan '/folders/myfolders';
proc export data=myfolder.ssbf03 DBMS=xls
outfile="/folders/myfolders/deals.xls";
run;

 

 

The error message:

ERROR: An exception has been encountered.
Please contact technical support and provide them with the following traceback information:
 
The SAS task name is [EXPORT (]
Segmentation Violation
 
Traceback of the Exception:
 
/opt/sasinside/SASHome/SASFoundation/9.4/sasexe/sas(+0x15cfde) [0x7feae59d3fde]
/opt/sasinside/SASHome/SASFoundation/9.4/sasexe/sas(+0x4cb7b) [0x7feae58c3b7b]
/opt/sasinside/SASHome/SASFoundation/9.4/sasexe/tkmk.so(bkt_signal_handler+0x144) [0x7feae428e404]
/lib64/libpthread.so.0(+0xf7e0) [0x7feae54477e0]
/opt/sasinside/SASHome/SASFoundation/9.4/dbcs/sasexe/sasyh(yhlalia+0x20) [0x7fead344ca70]
/opt/sasinside/SASHome/SASFoundation/9.4/dbcs/sasexe/sasyh(yyhrc+0x39) [0x7fead343e7d9]
/opt/sasinside/SASHome/SASFoundation/9.4/dbcs/sasexe/sasyh(yyorc+0x400) [0x7fead3447870]
/opt/sasinside/SASHome/SASFoundation/9.4/dbcs/sasexe/sasyoio(yyoopen+0x9b9) [0x7feaa447e0b9]
/opt/sasinside/SASHome/SASFoundation/9.4/dbcs/sasexe/sasyoio(yoopen+0x1eb) [0x7feaa447d32b]
/opt/sasinside/SASHome/SASFoundation/9.4/sasexe/sasimctr(cdsdata+0x1cf) [0x7fea7f5e79cf]
/opt/sasinside/SASHome/SASFoundation/9.4/sasexe/sasimctr(exportf+0x2be) [0x7fea7f5e776e]
/opt/sasinside/SASHome/SASFoundation/9.4/sasexe/sasimctr(parsexe+0x1e4) [0x7fea7f5e4a74]
/opt/sasinside/SASHome/SASFoundation/9.4/sasexe/sasexpor(sasexpor+0x126) [0x7fea7f7ffa86]
/opt/sasinside/SASHome/SASFoundation/9.4/sasexe/sas(vvtentr+0x13d) [0x7feae58c371d]
/lib64/libpthread.so.0(+0x7aa1) [0x7feae543faa1]
/lib64/libc.so.6(clone+0x6d) [0x7feae4acdaad]
 
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
 
Could you please help me in that, suggest any other way to convert the file to excel.
Thank you very much.
Super User
Posts: 7,405

Re: Export dataset from SAS university edition to excel

Your post does not make sense:

libname Dealscan '/folders/myfolders';
/* here you assign a libname, but you do not use that library */
proc export
/* this does not IMPORT anything, it EXPORTS, just as the name says */
  data=myfolder.ssbf03
  /* here you use library myfolder, but you did not show the corresponding libname statement */
  /* you also use a dataset named ssbf03, which you did not mention in your post */
  DBMS=xls
  outfile="/folders/myfolders/deals.xls"
  /* so "deals" is not a SAS dataset, but a Excel file to be created */
;
/* and you never used a folder "dealscan" anywhere within /folders/myfolder */
run;

So please re-state what exactly you want to accomplish.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 6

Re: Export dataset from SAS university edition to excel

Hi 

Super User
Posts: 7,405

Re: Export dataset from SAS university edition to excel

[ Edited ]

So you first need to assign a library name to the physical path where your .sas7bdat file resides:

libname dealscan '/folders/myfolders/dealscan';

Next, you can use that to address your dataset:

proc export
  data=dealscan.deals
  outfile='/folders/myfolders/deals.xlsx'
  dbms=xlsx
;
run;

I used the modern xlsx format, as that engine is platform-independent.

 

Edit: corrected name of myfolderS.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 6

Re: Export dataset from SAS university edition to excel

Hi,

Thank you very much for your cooperation.

The commands seem to work but I think they didn't export the file because the file to excel because the number of rows exceeds the 1,048,576 maximum rows in excel. I got the below error message:

 
NOTE: Data file DEALSCAN.DEALS.DATA is in a format that is native to another host, or the file encoding does not match the
session encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce
performance.
ERROR: XLSX file cannot be created -> /folders/myfolders//DEALS.xlsx. Make sure the path name is correct and that you have
write permission.
ERROR: Too many records for the output file
NOTE: "/folders/myfolders/DEALS.xlsx" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 58.45 seconds
cpu time 58.36 seconds
 
Is there a way to split the SAS file into 2 excel spreadsheets/worksheets?  or to delete first half rows from the SAS file and export second half, then delete the second half and export the first? 
Thank you very much. 
Super User
Posts: 7,405

Re: Export dataset from SAS university edition to excel

Excel is not suited for such amounts of data, so the export makes no sense. Continue your analysis in SAS until you've either reached your goal or a sufficiently low number of observations.

My personal view is that Excel (a spreadsheet calculator) should not be used for data exceeding three screens, because the operator always needs to be able to control the whole sheet.

If you just need to transport your data to another application, use a sensible, text-based format (CSV).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 392

Re: Export dataset from SAS university edition to excel

@KurtBremser is absolutely correct - spreadsheets of this size are extremely difficult to work with. I remember many years ago being asked by a customer if I could help them speed up a large spreadsheet recalc. When I asked how long it had been recalcing for they said 'Three days".......

Occasional Contributor
Posts: 6

Re: Export dataset from SAS university edition to excel

Ok, I'll do the analysis in SAS and then export the data.

Thank you very much for your cooperation.

Super User
Posts: 11,114

Re: Export dataset from SAS university edition to excel

As one small example of @KurtBremser's suggestion:

20 years ago I was working with an organization that had graduate students working that had not really been exposed to real world data. So when asked to look at some data he brought it into Excel and tried to find the extreme values for one of the variables. When the process did not complete afteer 4 hours he asked for some help. At which point I was able to point out that the file he attempted to use was roughly 3 times the number of records Excel was designed to handle at the time, that we had a data repository with the same data in SAS data sets, that he had SAS installed on his work computer for a reason, and showed him the 6 or so lines of code to do the same thing he had attempted in Excel that completed in roughly 2.5 seconds.

 

And I think Kurt is being generous with the 3 screens. The only time I use more than one is when I have to update someone else's design. I very seldom use more than 40 rows and 15 columns in a worksheet.

Ask a Question
Discussion stats
  • 8 replies
  • 198 views
  • 3 likes
  • 4 in conversation