09-25-2017 08:40 AM - edited 09-25-2017 08:42 AM
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
The error message:
09-25-2017 10:52 AM
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.
09-25-2017 11:04 AM
Thank you for your reply.
Actually, I'm new to sas, that is why I'm confused.
I have a sas file named "deals" with this extension 'sas7bdat' in a folder named "dealscan" within this path '/folders/myfolders'
I want to convert it to excel through sas universsity edition.
09-25-2017 11:22 AM - edited 09-25-2017 11:23 AM
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.
09-25-2017 12:15 PM
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:
09-25-2017 05:15 PM
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).
09-25-2017 07:48 PM
@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".......
09-26-2017 06:24 PM
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.