BookmarkSubscribeRSS Feed
tomcmacdonald
Quartz | Level 8

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.  

13 REPLIES 13
SASKiwi
PROC Star

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

tomcmacdonald
Quartz | Level 8

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. 

Reeza
Super User

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

 

 

tomcmacdonald
Quartz | Level 8

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.

Reeza
Super User

 

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;
tomcmacdonald
Quartz | Level 8

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. 

Reeza
Super User

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

 

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

tomcmacdonald
Quartz | Level 8

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.

ballardw
Super User

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.

 

Reeza
Super User

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...

tomcmacdonald
Quartz | Level 8

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

TaniaRTP
Obsidian | Level 7

I was able to use this function on all character variables and no longer got the corrupt xlsx file.  I read in an Excel sheet (my source which had query response text from sites all over the world), manipulated & summarized the data, then needed to output it back to a workbook with other sheets.

Patrick
Opal | Level 21

@tomcmacdonald 

Just as a side note: The PRX...() functions don't work for multi-byte characters and though your test might be invalid.

http://support.sas.com/documentation/cdl//en/nlsref/69741/HTML/default/viewer.htm#p1pca7vwjjwucin178...

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 6436 views
  • 7 likes
  • 6 in conversation