DATA Step, Macro, Functions and more

SAS 9.4 exporting corrupted XLSX files

Reply
Frequent Contributor
Posts: 91

SAS 9.4 exporting corrupted XLSX files

[ Edited ]

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. 

 

edit:

Downloaded the SAS dataset and tried repeating the process on PC SAS 9.4 with the same results.

 

edit2:

Wound up just using pandas.  I'm thinking it might have been an encoding issue.  

Super User
Posts: 3,860

Re: SAS 9.4 exporting corrupted XLSX files

Posted in reply to tomcmacdonald

Using Excel to keep data types consistent?! This sounds like a good oxymoron to me.

Frequent Contributor
Posts: 91

Re: SAS 9.4 exporting corrupted XLSX files

What I mean is I don't want to export it to a CSV then have to go into each column in Excel and change the data type to the appropriate one. 

Super User
Posts: 23,296

Re: SAS 9.4 exporting corrupted XLSX files

Posted in reply to tomcmacdonald

How are you moving the files?
Did you set the transfer type to binary?

 

 

Frequent Contributor
Posts: 91

Re: SAS 9.4 exporting corrupted XLSX files

[ Edited ]

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.

Super User
Posts: 23,296

Re: SAS 9.4 exporting corrupted XLSX files

Posted in reply to tomcmacdonald

 

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;
Frequent Contributor
Posts: 91

Re: SAS 9.4 exporting corrupted XLSX files

[ Edited ]

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. 

Super User
Posts: 23,296

Re: SAS 9.4 exporting corrupted XLSX files

Posted in reply to tomcmacdonald

So what's in your data that's problematic?

 

Any invisible characters, special characters or otherwise formats that may cause issues?

Frequent Contributor
Posts: 91

Re: SAS 9.4 exporting corrupted XLSX files

[ Edited ]

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.

Super User
Posts: 13,321

Re: SAS 9.4 exporting corrupted XLSX files

Posted in reply to tomcmacdonald

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.

 

Super User
Posts: 23,296

Re: SAS 9.4 exporting corrupted XLSX files

Posted in reply to tomcmacdonald

Binary search? 

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:

 

a-character-b

http://documentation.sas.com/?docsetId=lrcon&docsetTarget=p0wphcpsfgx6o7n1sjtqzizp1n39.htm&docsetVer...

Frequent Contributor
Posts: 91

Re: SAS 9.4 exporting corrupted XLSX files

OK, that sounds like a good strategy.  Thanks for all the help everyone.

Ask a Question
Discussion stats
  • 11 replies
  • 415 views
  • 6 likes
  • 4 in conversation