01-17-2018 01:41 PM - edited 01-17-2018 02:23 PM
I'm exporting using PROC EXPORT on SAS 9.4 UNIX. The SAS logs says the file was created successfully. However when trying to open it in Excel 2010 SP2 it says it has found unreadable content. Is there any workaround for this? I need to export to Excel and not a text file because I need to keep data types consistent.
Downloaded the SAS dataset and tried repeating the process on PC SAS 9.4 with the same results.
Wound up just using pandas. I'm thinking it might have been an encoding issue.
01-17-2018 02:54 PM - edited 01-17-2018 02:59 PM
scp through a SSH connection. I'm transferring it as a binary file.
edit: Let me try downloading it with a different program and see if that's it.
01-17-2018 02:59 PM
I'm assuming this happens regardless if you create the file with PROC EXPORT or ODS EXCEL?
You should also show your code...
To narrow down the problem, does a basic export work, ie If you run the code below and move the file are there any issues?
If not, it may have something to do with your data.
proc export data=sashelp.class outfile='/folders/myfolders/myfile.xlsx' dbms=xlsx replace; run;
01-17-2018 03:08 PM - edited 01-17-2018 03:09 PM
The export code looks like this:
PROC EXPORT DATA=MYSASDATASET OUTFILE='/some/path/file.xlsx' DBMS=XLSX REPLACE; RUN;
The code you pasted works with the original process I was using. I'm getting a non-corrupted Excel file.
01-17-2018 05:14 PM - edited 01-17-2018 05:27 PM
I thought it might be unprintable characters because I'm working with some sloppy data. However after going through each character column and using this:
BAD_COLUMN = PRXCHANGE('s/[^\x20-\x7F]//', -1, BAD_COLUMN);
i.e. only allowing ASCII characters, I still get a corrupted export.
No special formats.
01-17-2018 05:37 PM
How many records and variables are we talking about?
If there are not a lot of records I might be tempted to print the character variables, likely to an RTF document, and see if anything looks funny in the output.
Or if you have a few longish text fields just print those as longer text seems to have more room for problematic data entry/capture issues.
If the set has a lot of records then perhaps you could get lucky looking at a couple of hundred lines.
01-17-2018 06:15 PM
TBH I don't know what the issue would be so guess and test is likely the quickest approach here.
Take approximately half your variables and see if the export works. If it does, check the next half, if it doesn't work, then take another half and repeat until you find the issue.
You can select a range of variables in a specific order using a -- b where a is the first variable and b is the last variable in the list.
You can also select only character - since this is likely to be a character issue with: